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?

ravish.hacker · Accepted Answer · 2016-03-16 12:28:48Z

16

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

answered Mar 16, 2016 at 12:28

ravish.hacker

1,19314 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mejdev Over a year ago

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

Peter Schott · Accepted Answer · 2014-04-17 18:06:14Z

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.

answered Apr 17, 2014 at 18:06

Peter Schott

4,73625 silver badges31 bronze badges

2 Comments

Paul Michaels Over a year ago

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?

Peter Schott Over a year ago

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)

Collectives™ on Stack Overflow

SQL Server project executing multiple script post deploy

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related