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

Question

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?

Answer 1

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.

Answer 2

Valid Error

This is because :r somescript.sql isn't valid sql. You are seeing a syntax error, which is precisely what the error message states.

SQL80001: Incorrect syntax near ':'.

SQLCMD Mode

The post deployment script is executed under SQLCMD Mode.

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches.

Resolution

Remember that you are able to connect to a DB and execute sql from Visual Studio. You would see this same syntax error if you opened the same script within SSMS. However, in both SSMS and Visual Studio, you can "enable" SQLCMD Mode.

VS 2017

• right click on the editor containing the sql script
• click Execution Settings
• click SQLCMD Mode
• Note that you can do the same thing from the SQL menu

SSMS 17, SSMS 18

• Query (menu item)
• SQLCMD Mode

See also

• docs.microsoft.com
• StackOverflow