DDL ‘IF not Exists” conditions to make SQL scripts re-runnable


As a part of continuous integration we are using deployment of database scripts, which makes very important to make the scripts re-runnable. Some checks for DDL elements are not obvious, and I decided to put hem in one place.Most answers were found on StackOverflow


Column does not exists
if NOT Exists(select * from sys.columns where Name = N’CreatedDate’ and Object_ID = Object_ID(N’MyTableName‘))

Check if primary key does not exists

Check if foreign key does not exists
or (from here)
IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = ‘FK_Name’)

Check if DEFAULT does not exist
if NOT Exists(select * from sysobjects where xtype=’D’ and NAME=’DF_MyTableName_MyColumnName’)

Check if an INDEX doesn’t exist
–from http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table
IF NOT EXISTS(SELECT * FROM sys .indexes WHERE name = ‘_index_MyTableName_MyColumnName_MyColumn2 AND object_id = OBJECT_ID(MyTableName ))
CREATE NONCLUSTERED INDEX [_index_MyTableName_MyColumnName_MyColumn2] ON [dbo].[MyTableName]
        MyColumnName ASC ,
       MyColumn2 DESC

Check if STATISTICS name doesn’t exist
IF NOT EXISTS(SELECT name  FROM sys .stats WHERE name = ‘_dta_stat_MyTableName_MyColumn1_MyColumn2 AND object_id = OBJECT_ID(MyTableName ))
CREATE STATISTICS _dta_stat_MyTableName_PDFAttachmentName_MailID_DateCreated ON [dbo].[MyTableName] (MyColumn1,MyColumn2)

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc') exec('CREATE PROCEDURE [dbo].[MyProc] AS select 1')
--body of your SP


 Check if VIEW doesn’t exist
From Books Online: “CREATE VIEW must be the first statement in a query batch.”
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]’ ))
exec(‘ create view MyView AS select 1′)
Alter VIEW [dbo]. [MyView]


Gmail arbitrary rejects emails with big number of recipients

I was sending information letter to the club subscribers(total 225 emails) using Gmail account. I keep them in 3 mailing lists: 2 with 95 emails and 3rd with remaining(GMail limit recipients to 100 per email). The first list was rejected with the following (not very specific) message.
Delivery to the following recipient failed permanently: email address
Technical details of permanent failure:
Message rejected. See https://support.google.com/mail/answer/69585 for more information.
But the second list with the same size was successful.
Additional annoyance was that “delivery failed” notifications were received slowly within 5 minutes, and initially I didn’t understand which group  (or emails from both groups)  were rejected.
 The simple workaround to split big list in 3 smaller mailing lists one was successful.

GMail considered “unsubscribe” at the end of email as spam

I’ve created a new email account @gmail.com to communicate with members of Russian Bards Songs Club . When I sent the first email to 15 people, it was rejected with the message:

Delivery to the following recipient failed permanently:

Technical details of permanent failure:

Message rejected.  See https://support.google.com/mail/answer/69585 for more information.

I’ve sent the same email to a single person and it was successful. I’ve wrote a small program to  vary some details and number of addressees, but the emails were continue to be blocked. I wasn’t able to send the email even to a single recipient. It means that gmail has more strict anti-spam criteria for smtp  clients then for gmail senders.

The email I was trying to send was written in Russian with some words in English and contained a few links. I didn’t know, what else make my email suspicious and submitted a question to Google. But they normally do not respond.

I’ve decided to test smaller emails to identify if particular part of content flags the email as spam. After a few attempts I found, that the trouble caused  the phrase

If you don’t want to receive more emails from us, please reply with the word ‘Unsubscribe’ in the subject.

I’ve seen multiple recommendations to include unsubscribe information in emails to bypass anti-spam filters. It seems that too many spammers started to add such sentences, that Google now consider it as a spam indication.

Maybe  they didn’t like that I have an instruction to unsubscribe via email instead of providing link to website.

For now, I removed the sentence from the email and finally was able to send it.


Couple weeks later I added a message “If you don’t want to receive emails any more, click on this link”( in Russian), but the actual URL contained the word “unsubscribe”. I’ve sent emails to 3 big groups of people(about 100 each). Two of them were sent successfully, but one was rejected(actually the first one).
I split the failed group in three smaller ones, and tried to send them. They were rejected again.

Additional annoyance was that “delivery failed” notifications were received slowly within 5 minutes, and initially I had an impression that some emails succeeded, and only some were rejected. However according to timestamp all emails were rejected at the same time.

Then I renamed the target URL to not use “unsubscribe” word, updated the link in email signature, and was able to send emails successfully.

FetchRemote function to work with git in Powershell

I have a useful method to create a remote git alias, if not exists,  and fetch data. I used it in Powershell scripts to operate with git remote branches:

function FetchRemote( $remoteAlias, $remoteRepository, $remoteBranch)
$aliasFound= & git remote | out-string -stream | select-string $remoteAlias
git remote add -f -t $remoteBranch –no-tags $remoteAlias $remoteRepository
git fetch $remoteAlias

bad revision ‘rm’ error, when using git filter-branch –index-filter if called from batch file

I was trying to move a few directories from one repository to another and answer http://stackoverflow.com/a/17867910/52277 suggests to use

    git filter-branch –index-filter ‘git rm –cached -qr –ignore-unmatch — . && git reset -q $GIT_COMMIT — apps/AAA libs/XXX’ –prune-empty — –all
I’ve put the command into batch file and got the error
> fatal: bad revision ‘rm’
The same error happened for an example from git documentation https://git-scm.com/docs/git-filter-branch
    git filter-branch –index-filter ‘git rm –cached –ignore-unmatch — buildMaster.bat’ HEAD
However when I was running the command in git bash, it didn’t  show any error and worked successfully.

It was acceptable for me. But I am still curious why including inside .bat cause an error with unclear message

#batch, #branches, #error, #git

RegEx for credit-card-numbers with spaces/dash delimiters

I’ve used regular expressions from http://www.richardsramblings.com/regex/credit-card-numbers/ that supports delimiters spaces/dashes, e.g. “3714-496353-98431” or “3714 496353 98431”. (by the way I’ve added Diner’s Club pattern  ((?:30[0-59]|(?:(?:36|38|39)[0-9]))[0-9]{11}) even they are obsolete now)
Unfortunately they use backreferences \1 in individual credit card matches, which doesn’t work if I use alternatives (Visa)|(Master)|(Amex).
I replaced numbered capturing groups with named capturing groups http://www.regular-expressions.info/named.html .
Javascript and .Net format for named groups are different. I’ve done initially using ?P syntax
(?<!\d)(?:(4\d{3}((?P[\ \-]?)(?:\d{4}(?P=DelimVI)){2}\d(?:\d{3})?)|(5([1-5]\d{2})(?P[\ \-]?)\d{4}(?P=DelimMC)\d{4}(?P=DelimMC)\d{4})|(3[47]\d\d(?P[\ \-]?)\d{6}(?P=DelimAX))\d{5}))(?!\d)
and then had to translate to .Net syntax
(?<!\d)(?:(4\d{3}(?’DelimVI'[\ \-]?)(?:\d{4}\k’DelimVI’){2}\d(?:\d{3})?)|(5([1-5]\d{2})(?’DelimMC'[\ \-]?)\d{4}\k’DelimMC’\d{4}\k’DelimMC’\d{4})|(3[47]\d\d(?’DelimAX’)[\ \-]?)\d{6}\k’DelimAX’\d{5}))(?!\d)
I’ve tested Javascript syntax in RegEx101 (see https://regex101.com/r/dX5zJ4/3), but for .Net I had to use http://regexstorm.net/tester and they are not comparable-RegEx101 is significantly user friendlier.

#credit-cards, #regex, #regular-expressions

IdentityServer OpenID Connect Flows: Relationship between Response Types and Grant Types

Openid Connect determine a few flows ( e.g. see scottbrady91 Flow Comparison  and which-openid-connect flow-is-the-right-one )

 In IdentityServer Client description you specify grant type (i.e. flow)( e.g. AllowedGrantTypes = GrantTypes.HybridAndClientCredentials )

You also specify one or more response types in OpenIdConnectOptions.ResponseType (e.g. ResponseType = “code id_token token”)

If you mismatch them, you will get error like.

[Error] Invalid grant type for client: implicit \”ResponseType\”: \”id_token token\”

The relationship is not obvious, it’s partially discussed in http://stackoverflow.com/questions/29275477/openidconnect-response-type-confusion.

To understand the possible relationships between Response Types and Grant Types see IdentityServer4Constants.cs


            { OidcConstants.ResponseTypes.Code, GrantType.AuthorizationCode },

            { OidcConstants.ResponseTypes.Token, GrantType.Implicit },

            { OidcConstants.ResponseTypes.IdToken, GrantType.Implicit },

            { OidcConstants.ResponseTypes.IdTokenToken, GrantType.Implicit },

            { OidcConstants.ResponseTypes.CodeIdToken, GrantType.Hybrid },

            { OidcConstants.ResponseTypes.CodeToken, GrantType.Hybrid },

            { OidcConstants.ResponseTypes.CodeIdTokenToken, GrantType.Hybrid }

#hybrid-flow, #idsrv, #openid-connect

Pocket Favorites are not available when you off-line

I am using Pocket(previously known as Read It Later) and sometimes sent to support my suggestions.
Unfortunately they do not have convenient support forums(or uservoice sites), so I post my suggestions here to have them visible:

IPad Pocket Favorites are not available when you off-line. It shows message similar to Archives. For Archives it makes sense, but why favorites have this limitation?
Could you please allow to access Favorites list off-line.

The support person agreed that including the Favorites would be great approach to improving upon this feature.

He also ask to submit the request officially here: http://pocket.co/soPybA This helps the team prioritize most requested features we receive.

I don’t understand why email to support is not considered as official request. Anyway everyone who wants to suggest something to Pocket, should fill the boring form

#favorites, #pocket, #support


Visual Studio 15 build Task could not find “sgen.exe”

On new Windows 10 machine with Visual Studio 2015 installed when I tried to build my solution from batch file, I’ve got an error

c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.Common.targets(2769,5): error MSB3086: Task could not find “s
gen.exe” using the SdkToolsPath “C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\” or the regis
try key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v8.0A\WinSDK-NetFx40Tools-x86”. Make sure the Sdk
ToolsPath is set and the tool exists in the correct processor specific location under the SdkToolsPath and that the Mic
rosoft Windows SDK is installed

suggested to Install Microsoft Windows SDK for Windows 7 and .NET Framework AKA v7.1.

But I’ve got error: Windows SDK Setup
Some Windows SDK components require the RTM .NET Framework 4. Setup detected a pre-release version of the .NET Framework 4. If you continue with Setup, these components will not be installed. If you want to install these components, click Cancel, then install the .NET Framework 4 from http://go.microsoft.com/fwlink/?LinkID=187668 and then rerun Setup.
The message is misleading, because I have .net framework 4.6, but I Tried to install the .NET Framework 4 from https://www.microsoft.com/en-us/download/details.aspx?id=17851
I’ve got expected message:
Same or higher version of .NET Framework 4 has already been installed on this computer

I’ve tried different suggestions trying to change path and properties in Microsoft.Common.targets, but finally gave up and

Copy sgen.exe, al.exe and al.exe.config from C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6 Tools\
to C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\

as suggested at http://stackoverflow.com/questions/4923131/task-could-not-find-al-exe-using-the-sdktoolspath

#build, #visual-studio

OutDir vs OutputPath for Visual studio 2015 and Asp.Net Core


In MSBuild for Asp.Net Core use /p:OutputPath parameter, not /p:OutDir nor both.

Full story

We’ve used psake powershell build for ASP.Net 4.61 project and passed both parameters
msbuild  /p:OutDir=“$binariesDir\” /p:OutputPath=“$outPath\”
Actually /p:OutputPath was ignored(almost, see below) and build was saved to specified in /p:OutDir folder.
When I included Asp.Net Core project into solution, it’s failed because dll, referenced by the Core project, was saved to OutDir folder, but Asp.Net Core project tried to find it in OutputPath folder.
I’ve tried to remove /p:OutputPath  parameter (as suggested in OutputPath vs OutDir thread: “to keep the OutputPath that is generated by Visual Studio, then set the OutDir as a command argument”), Asp.Net Core project failed because dll was saved to OutDir folder, but Asp.Net Core project tried to find it in relative bin\Release\ folder.
When I removed OutDir parameter and specified /p:OutputPath=“$binariesDir\” build was successful,  but package for MSDeploy, specified in a custom MS Build step, was created in a relative path obj\Release\Package rather than specified $binariesDir folder, so I needed to adjust package path.
Current VS 2015 MSDN page  Common MSBuild Project Properties doesn’t document OutDir , but only OutputPath.
Note that previously MSDN recommended the opposite: “OutputPath has been deprecated and OutDir should be used instead whenever possible. ”