12

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paul Michaels
  • 16,185
  • 43
  • 146
  • 269

2 Answers2

16

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

enter image description here

Or you can do this by going to

enter image description here

ravish.hacker
  • 1,189
  • 14
  • 21
8

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.

Peter Schott
  • 4,521
  • 21
  • 30
  • 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? – Paul Michaels Apr 28 '14 at 10:21
  • 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 http://www.tested.com/tech/windows/39-using-symlinks-in-windows-for-fun-and-profit/ -- 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) – Peter Schott Apr 28 '14 at 16:30