Visual Studio 2010: Is there a way to deploy a single database object that retains the object in normal builds?

Developers have expressed a desire to deploy a single database object from a Sql Server 2008 project, such as a stored procedure, without having to use the build/deploy or schema comparison functions.

To enable this, developers have created their database object scripts including 'if exists.. drop' checks at the top of the script and have included the grant statements for the objects in their scripts.

This results in build errors that then prevent the build/deploy or schema compare functions from operating. So then, developers mark the object as "not in build" but then the objec开发者_C百科t can't be deployed at all using build/deploy or schema compare.

Has anyone found a way of quickly deploying a single database object from visual studio that does not involve schema compare or build/deploy which does not remove the object from the normal build process? Manual copy/paste is not an option but scripting/macros which effectively do the same would be viable.

SQL Server Data Tools (SSDT) now provides this functionality by way of comparison. Individual differences identified in the comparison may be deployed. We have found that during development, publishing tends to result in overlaying the simultaneous changes that other developers are making on the shared development database server. The comparison tool has been working fairly well, except for a very annoying crash issue that occurs when the comparison window is closed. We are using 32bit Vista with 3GB of RAM and VS 2010. This issue may not occur in other configurations.

First I'd like to comment that you seem to be fighting the intended paradigm with regards database projects.

• If your project is deployed somewhere [1],
• then there should be a corresponding branch / label [2] in your source repository.
• If you change a single object [delta] from the above baseline [2], and build the new version [3],
• then when you deploy [3] to [1], the deployment script should find only one difference, and the net effect of the change [delta] is all that will be applied.

So, in theory there's no reason not to just simply build and deploy.
TIP: When taking on a new paradigm, it pays to embrace it fully; partial adoption tends to cause its own set of problems.

However, that said, I may be able to help.

We have a similar need because actual deployment is out of our control. We control only part of the database, and have to provide our changes to another team for review.
We have to provide individual 'self-contained' scripts for each object with if exists..drop at the drop and grant permission at the bottom.
However, we want the other benefits of working with the database project, and then simply copy out the individual script files when we "deploy".

The solution we came up with was to place the extra "bits" in a comment block as follows:

/*SINGLE_OBJECT_DEPLOYMENT
if exists (...)
  DROP ...
--*/

--/*SINGLE_OBJECT_DEPLOYMENT
if exists (...)
  DROP ...
--*/

Note that a simple search and replace of /*SINGLE_OBJECT_DEPLOYMENT with --/*SINGLE_OBJECT_DEPLOYMENT enables the commented out code, so it can be easily put into a macro, or other semi-automated process.