How run command line exe and/or batch files from the Script.PostDeployment.sql in a VS2010 Database Project?

Question

Hi

I've got a VS2010 SQL Server 2008R2 Database Project, which I'm using as the definitive source for my project database. This works well, after each significant code change to my Web App I rebuild and deploy a local copy of my database, which then has my test data set up for me. A problem I have hit however is that when I deploy my database, IIS gets confused about the database it is connected to- thinking it has lost the old one but not realising it has got a new version of the same thing. Now there are various articles about using SqlClient.SqlConnection.ClearAllPools()  to fix this on the net but this doesn't seem to work for me. What does work, however is if I do a full restart on IIS after each dtabase deploy and given its a local development environment that's fine. What I'd like to be able to do therefore is have a call to the following commands from my Script.PostDeployment.sql so this happens automatically on local deployment...

net stop WAS /y
net start W3SVC

How do I do that ( in SQLCMD or otherwise) from the Script.PostDeployment.sql? Or is there some better way  to call post deploy OS commands?

Answer 1

Hello DaveSCT,

As far as I know that we can only use SQL scripts in the database projects, so I do not think it is supported to use batch file or other non-sql commands in the Script.PostDeployment.sql script.

However, for your scenario, I think you can consider putting the vsdbcmd.exe command in the batch file to deploy your database project. After you deploy your database project deployed successfully, you can then run the net stop/start command in the batch file.

For further information about vsdbcmd.exe, see: http://msdn.microsoft.com/en-us/library/dd193283.aspx

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thanks Vicky

I'm not sure I understand "putting the vsdbcmd.exe command in the batch file to deploy your database project". Currently I am deploying by right clicking on my DB project and selecting "deploy". Then I have to go outside VS and run my RestartIIS.bat. Is there a batch/script file behind the VS "deploy" action that I can tailor? If so that's great

- or are you saying I should create a new DeployAndResetIIS.bat that uses vsdbcmd to deploy and then restarts IIS ...and then I should disregard the VS "deploy"?

Answer 3

Hi DaveSCT,

As we know that we can deploy database project with vsdbcmd command, for example VSDBCMD /a:Deploy /dd:+ /manifest:manifestFileName.dbmanifest . The approach I mentioned above means that you first create a batch file and then put the vsdbcmd and net stop/start command in it. Maybe your batch file is similar to:

VSDBCMD /a:Deploy /dd:+ /manifest:manifestFileName.dbmanifest
net stop WAS /y
net start W3SVC

It is very convenient to deploy database project directly in VS IDE, however, for your scenario run batch file in the database project is not supported, so I think that workaround should work for you.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Vicky

We're moving forward  but I'm not quite there yet. Following your example above I'm wanting to do a deploy that's largely defined by your manifestFileName.dbmanifest. So my question is how do I create a dbmanifest that uses the definition held by my VS2010 Database project? Note that the various components of the definition for VSDBCMD need to be as specified by my Database project AND NOT the result of a "right click Deploy" of my project (If I had to do a right click deploy then run a batch file then I'm no further forward than I was originally).

Sorry if I'm being dim here but in simple terms can you give me an example of either

a VSDBCMD command with command line parameters that use the specification held  by a VS2010 Db project or

The contents of a dbmanifest that does the same thing.

Thanks

Answer 5

Hi DaveSCT,

Sorry for the late reply, I do not get the notification email for some reason. And do you still not have your issue resolved? You need to get your issue resolved with the approach I provided above. And I can't better understand what you would like to keep in the deploymanifest file, do you mean you would like to add the command in it? As far as I know that when we build a database project one deploymanifest file is generate, and I do not think you should modify it manually.

If I misunderstand your issue, please correct me.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hi Vicky

"As far as I know that when we build a database project one deploymanifest file is generate"- I think this is where my problem is. Maybe I'm being dim but I don't see where the VS build process creates a manifestFileName.dbmanifest type file? It does create a Database.sqldeployment but this appears to be different. The sqldeployment  for example  doesn't contain info on where we're deploying to.

Answer 7

Hi Dave,

When you build a database project, you will get one .deploymanifest file. You should find it in the same folder where you get the .sqldeployment folder. By default the build output path is .\sql\debug\.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 8

Ah Thanks Vicky. Hidden of course but do I feel silly now. A lot of things make much more sense  ...