SQL Server project executing multiple script post deploy

Question

I have a SQL Server 2008 DB Project, and want to execute multiple scripts, in order, on post deploy.

I've added a PostDeploy.Sql script to a scripts folder, and marked that as PostDeploy, and that works fine. I'm aware that only one script can be marked as post deploy; so I thought I could do this within the script:

-- Post Deploy stuff
SELECT * FROM MYTABLE
GO

:r RunPostDeploy2.sql
GO

Where RunPostDeploy2.sql is located within the same directory as a link. I've tried copying the file locally, but the issue seems to be the use of :r.

Is it possible to call external scripts from post deploy in this way and, if so, how?

Overstudy

Answer 1

You have to turn your script into SQLCMD mode. There is a toolbar button right on the top of the file like this.

Or you can do this by going to

Mosora

Ah; you answered this just in time with the exact answer I needed! Thanks! – Paderna

Answer 2

First, you can only have one script set as "post deploy" (usually set up for you in the older DB Projects).

When using a post-deploy script, you include the others using:

:r .\MyScript1.sql
:r .\MyScript2.sql
:r .\NestedFolder1\MyScript25.sql
--etc

It looks like you're missing the ".\" which tells the run command to look in that folder.

Curdle

Yes, and no. Adding the .\ does give me the correct error - which seems to be that it can't locate the called SQL file from the current directory (in which it only exists as a link). Are we saying that it is not possible to call scripts fro a VS link? – Overstudy

Pretty sure that it won't recognize a link. If you look behind the scenes, those link files have an "lnk" extension and are just a pointed to the actual file. You could look at using a symlink tested.com/tech/windows/… -- but you're usually better off storing the actual scripts with each project unless they're truly shared. (even then, I'd make the symlink as a folder under your post-deploy folder) – Curdle