SQLCMD variables not recognized by SSDT (build fails when these are used in a script)

Question

I am trying to utilize SQLCMD variables within an SSDT project.  I've defined a variable in the project properties -> sqlcmd variables tab and then from within my script I reference that variable using $(variablename).  However SSDT is flagging this as a syntax error (either SQL80001 or SQL46010) instead of recognizing that the variable has been defined at the project level.

I may be missing something obvious however after trying different things searching for some time I cannot seem to figure this out.

Here is an example (Dec CTP on VS 2010SP1).  Any thoughts appreciated.

I have defined the command variable as follows:

I then try to use this in code but the build fails with the below error message:

Answer 1

In the editor viewing that script, in the toolbar, there is a button called SQLCMD Mode. Click to toggle that ON. (You can also find it in the context menu when you right click in the script body, in the flyout menu on Execution Settings). This will tell the system to recognize the user-defined SQLCMD variables when analyzing the TSQL in the editor.

Are you using this in a post-deployment script? The project will actually still build and deploy using the $(DataFileSize) variable and value, even if you have this script open with errors. The SQLCMD mode toggle doesn't persist with the file, it's just an editor state so that you can validate the script with variables at design time and debug accurately.

Let me know if that doesn't work!

Thanks,

Sarah

---

Sarah McDevitt Program Manager SQL Server Data Tools