VS2012 Post-Deployment script referring to several other scripts
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.
Answer
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:
