Tuesday 8 January 2008

VS Db Pro – Script Deployment - Database Context

When deploying databases using Db Pro the scripts which are executed have a database context. Visual Studio will automatically include a 'USE [YourDbName]' where YourDbName is taken from the project's BUILD property. This is the context for the scripts to execute. This is important because if you change it in any of the scripts it can mess with your deployment.

For example, you create a script called 'AgentJobs.sql' in the Post-Deployment folder and add a reference to the script in Script.PostDeployment.sql. This means your AgentJobs script will be executed when you do a deployment. Now assuming you add a 'USE MSDB' statement in the script, your deployment runs successfully HOWEVER any scripts which execute after AgentJobs eg Data.sql (it executes in alphabetical order) will now have MSDB as the context rather than [YourDbName] as expected.

So make sure that you use fully qualified references to objects in other databases and DON'T use 'USE'!