One of the challenges that most every developer faces at one time or another is the challenge of pushing database changes to their production environment. For some time now I have been using the database management tools in Visual Studio 2008 Database Edition. One of the great things about this tool is that it allows you to perform both schema and data comparisons between different databases or between the project schema and a database. Consequently those changes can also be synchronized through Visual Studio or by generating a change script.
In my organization changes to a database that involve schema objects must be performed by a DBA although the actually SQL scripts are often written by the developer. The easiest way for me to provide the appropriate script for the DBA is to simply use the Schema Update Script that is generated by Visual Studio by default. To show the script simply click the “Show Schema Update Script” button. This will force the Schema Update Script panel to be displayed. Often I simply copy the text out of that panel and paste it into a text file to send to my DBA. He can then open the script in SQL Server Management Studio and run it from there.
While explaining this process to a member of my development team I realized that there is one very important step that you need to take in SSMS to be able to successfully run the script.
By default the Query Editor in SSMS cannot run a query that has scripting in it compatible with SQLCMD. Therefore it is necessary to turn on the SQLCMD mode in SSMS. So, here it is, in SSMS simply click on the Query menu and then toggle on SQLCMD mode menu item.
Now you can run the Schema Update Script successfully!
Tags: