28

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?

Improve this question
1
  • sorry I guess to be clearer "what action is the script set to that has the ":r" inside it?" :)
    – Ed Elliott
    Sep 7, 2015 at 16:08

2 Answers 2

Reset to default
27

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.

Improve this answer
2
  • Is there anyway to make SQLCMD Mode persist? My file randomly shows it erroring, so I have to re-set the mode each time.
    – Alex K
    Apr 28, 2020 at 19:41
  • 2
    @AlexK Yes, but doing so will mean that it is always on for all SQL scripts, which means that IntelliSense won't be on (if that matters). Just go to Tools -> Options -> Query Execution and there is a check-box on the right side for "By default, open new queries in SQLCMD mode".
    – Solomon Rutzky
    Apr 28, 2020 at 20:42
9

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

Improve this answer
1
  • shame there's not some text you can put in a file to get those ides to change their mode automatically.
    – JJS
    Feb 1, 2021 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.