November 15, 2013 | Posted in: Visual Studio 2012
There have been many trials and tribulations during the upgrade process from Visual Studio 2010 to Visual Studio 2012, not least the SQL Schema Compare functionality.
Having done the initial upgrade between the previous to the new version of Visual Studio, all .dbproj files were converted over to the new .sqlproj format. If you have written any SQL CLR projects in Visual Studio 2010 i.e. .csproj format, these too will get converted to .sqlproj files.
The first thing you will realize is that the references from your new .sqlproj file will throw syntax errors saying that your schema objects i.e. sprocs/functions etc can no longer resolve your CLR proj references. To get around this, go to properties on the reference too your CLR proj file, and change property “Model Aware” to True, this will allow you to compile again successfully.
The new way of doing a schema compare is to create a schema compare file. From with the VS IDE, go to SQL -> Schema Compare -> New Schema Compare. It’s important to note that the settings regarding which types to ignore are now held in this .scmp file. Once you’ve been through the settings and configured it to your liking, check this file in to TFS so that you can reuse it, otherwise you’ll end up having to go through these settings time and time again.
If you try and compare your new .sqlproj file to a SQL Server database, you’ll notice that the two “Update” and “Generate Script” buttons are disabled.
Through a lot of digging, I eventually found that the reason for this is that under your .sqlproj settings, you need to set a “Default” value on any “SQLCMD Variables” you may have included (this is the name of the option on the left hand side). Having done this, the options should become available (note you may need to restart the solution).
The behaviour of these SQLCMD Variables seems to be that it copies the values to your .sqlproj.user file, which the schema compare relies on. If you do not set a default value, you won’t be able to do a compare and generate scripts. The value you provide in the “Default” value will be stored in the project file and can be checked in to source control, however the “Local” value is specific to your machine which gets stored in your .sqlproj.user file I mentioned previously (which is not under source control). If you leave the Local value blank, the Default value will be used.
If this helped solve your issue, leave a comment and let me know.