This matter has been eluding me for well over half a year, and I am so glad to finally discover where the fault lies in a Visual Studio [Team Edition for Database Professionals] database project, that causes SQLCMD variables checked in the Script.PostDeployment.sql file like
IF '$(DeployTestData)' = '1'
/* INSERT TEST DATA */
/*Reporting data*/:r .\Data_Reports\SpecCurrent.sql
/*data acquisition from external databases*/:r .\Data_Acquisition\Util.DataAcquisitionLog.sql
to just choke and bang into
C:\Projects\main\Database\Scripts\Post-Deployment\Script.PostDeployment.sql(51,0): Error TSD164: Syntax checking failed : Incorrect syntax near END.
Even simply defining an empty BEGIN-END block is fatal. I cannot believe there are so few people in the world who ever want to selectively limit the amount of data inserted into databases depending on build configuration. The culprit? Well there are two things, actually:
- The IF-BEGIN-END block somehow cannot exist in Script.PostDeployment.sql itself. This type of blocks have to live within the individual script files (included by the :r directive) that insert data.
- Cannot use a numerical value to make the boolean check. It is best to use a string comparison.
IF '$(DeployTestData)' = 'true'
Why is this so? I have no idea. But is finally one long-hurting thorn removed from my side.