why be normal?
Welcome to Sign in | Help
in Search

Conditional data deployment for Database projects

Last post 04-10-2008, 12:40 by icelava. 0 replies.
Sort Posts: Previous Next
  •  04-10-2008, 12:40 2756

    Conditional data deployment for Database projects

    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'


    /*Reporting data*/
    :r .\Data_Reports\SpecCurrent.sql
    :r .\Data_Reports\Spec.sql
    :r .\Data_Reports\Submission.sql
    :r .\Data_Reports\Dispatch.sql
    :r .\Data_Reports\Consumption.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:

    1. 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.
    2. 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.

View as RSS news feed in XML
Powered by Community Server, by Telligent Systems