Why is :r SQLCMD command marked as wrong in Post Deployment Script?

I have worked a couple of times with post deployment scripts and always, intuitively used the build action "PostDeploy", because that is what it is.
Now for the first time I try to follow the built-in instruction from the script's template to use the ":r somescript.sql" syntax.

Immediately this line is getting marked as wrong:


"SQL80001 wrong syntax next to ':'"

I found suggestions to set the PDS to Build Action "none". This does not help, the error stays. What am I missing here?

Assuming that the error happens during the build process, and that you do not have any actual syntax errors, and it is a simple

:r path\to\file.sql

then the error also occurs when the file being imported does not exist. Please check the location of the file. If you did not provide an absolute path, then the path will be relative to the solution folder (at least for me it is).

If, while developing, you want to see the error that the build process reports, then make sure to enable "SQLCMD mode" in the T-SQL editor in Visual Studio. There is a button on the far right in the button bar with a "!" in it that should enable this. Or you can go to the SQL menu, select Transact-SQL Editor ->, select Execution Settings ->, then finally select SQLCMD Mode. Now when you Execute the script, it will correctly interpret :r, just like the build / publish process does.

Also, your Post Deploy Script (PDS) needs to have its Build Action set to PostDeploy. If it is set to None then it will be skipped entirely by the SSDT build process.

StackExchange Database Administrators Q#114332, answer score: 30