VS2012 Post-Deployment script referring to several other scripts

Question

I have created a database project in VS2012. I added database structure. It works fine to publish and compare database.

Now on publish I want to automatically load several tables with default data.

I created five script files with INSERT statements generated from SQL Management Studio. They were added to a Script folder in my database project.

Then I set BuildAction=PostDeploy. This works fine. BUT from some reason it is only possible to have ONE script set to PostDeploy ....

I realize I can move all scripts into one file. But I have a lot and would really like to group them in separate files to maintain some order.

I then created one PostDeploy.sql file and tried to reference all other script files from there. The file header gives directions:

Post-Deployment Script Template                         
----------------------------------------------------------------------------
This file contains SQL statements that will be appended to the build script.
Use SQLCMD syntax to include a file in the post-deployment script.
Example:      :r .\myfile.sql

So I write my file:

:r .\MyScript1.sql
:r .\MyScript2.sql
:r .\MyScript3.sql
:r .\MyScript4.sql
:r .\MyScript5.sql

The file complains on wrong syntax.

Procaine

Answer 1

It turned out that Visual Studio fooled me with wrong syntax warnings! My setup was perfectly valid.

To avoid warnings right click anywhere inside the file and choose "Execution Settings - SQLCMD Mode".

There is also a toolbar button named SQLCMD Mode doing the same thing.

Here is the menu item in case you can't find the toolbar button:

Procaine

This solves the problem of the Transact-SQL editor thinking that it is a syntax error. But an SSDT project still fails to build or deploy. – Raila

Try to completely close out Visual Studio. Clean your SSDT solution by deleting the bin and obj folders, the *.dbmdl, *.suo, and *.user files then re-open your SSDT solution. You should now be able to build/deploy your project. – Unamuno

@Raila Take a look in the Output window (instead of the Error List) - you should see the actual error there. In my case it was that my included files had spaces in their paths, and the whole path needed to be enclosed in double-quotes. No cleaning/restarting needed. – Chorography

Answer 2

You may also need to check and see what changed in your Project File. I ended up accidentally renaming my post deploy script and that caused this error. Fixed the issue with the post deploy script in the Project file and it started working correctly again.

Toddtoddie