The back end of one of the products I have been working on recently required some schema changes (new tables, amended tables, new SPROCS etc). So the team completed the work and the time came to deploy to the live system.
So Earlier this week I decided that I would use VS2008 for Database Professional (Data Dude) to generate a deployment script using the Schema Compare feature.
Now, all is well when you are testing the script using "Write Updates" against a DB in a development environment. This is because you have the required access and permissions to that SQL Server. However if you are deploying to a Live SQL server in a DMZ, chances are you are not going to have the same level of access.
Fortunately the deployment script can be exported to a script file! Excellent, now all I had to do was schedule the downtime and run the script against the live server.
This is where I was caught out and as I am not a DBA I spent a fair few frustrating minutes trying to figure things out. So, what was going wrong? Why would the script fail and flag syntactical errors in the script? Then I spotted “:Setvar…” in the script and looked the syntax up on Google. I found that this was SQLCMD syntax. By default SQLCMD mode is not enabled for T-SQL queries.
The solution (applies to SQL Server 2008):
1) Open your generated deployment script in the T-SQL query editor using Microsoft SQL Server Management Studio.
2) From the Query menu select SQLCMD Mode.
3) Execute your script.
And it’s that simple. No doubt there are plenty of people out there who know this, for those that don’t, I hope this helps.