CommuneIT

It’s been far too long since I last blogged and this one will act as another reminder for me.

I have been doing a bit of MVC lately, part of a management app for a series of web based applications... Part of the app which handles licensing was migrated from a previous project which was using aspx views. The new project is using Razor views.

I decided to re-create the views in Razor as they were fairly standard and would not take long to convert. With VS2010 twisting about the views existing when I attempted to re-create a view, I decided to exclude the existing views from the project. Great, with the new view in place I spun the project up and hey presto the data was not being displayed as expected???

The project was building fine, everything that needed to be in place was in place but, the page was rendering a view which was completely different to what had been coded up...

So after a while I figured out that the view which was being rendered was in fact the view which had been excluded from the project! Moral of the story… delete views don't exclude them otherwise you may go a little bit mental.

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.