SSDT: Target Platform and Compatibility Level

I have just seen an issue in one of my database release scripts where I’m getting a load of statements like the following….

PRINT N'Altering [dbo].[TestQueue]...';


PRINT N'No script could be generated for operation 2 on object ''[dbo].[TestQueue]''.';


This is because of the “Target Platform” setting on the properties of the project.  For this database it was set to 2008 as follows:

This needs to be set to the version of the SQL Server that the database sits on in live.  In this case it was SQL Server 2014.

When I changed this it automatically updated the compatibility level of the database to 2014.  However, although sitting on a SQL Server 2014 instance this database is still on 2008 R2 compatibility level so I had to click on “Database Settings” then click on the “Miscellaneous” tab and change it back to 2008 as follows.

Then when I generated the scripts again all the service broker queue alter statements were gone.

In summary, the “Target Platform” needs to be the version of the SQL Server Instance and the “Compatibility level” is the compatibility level of the database itself.


SSDT in Visual Studio 2013

Prior to Visual Studio 2013, SQL Server Data Tools (SSDT) was a stand alone application that you could install in the VS2010 or VS2012 shell.  Now it’s one of the optional extras in the install of VS2013.

SSDT for VS2013 1

I’ve installed VS2013 with the  SSDT optional feature and opened an existing database solution that was originally created in VS2012.  The solution opens fine and there is no upgrade required by the looks of it.  At first glance it looks like it will all just work okay.

I was then able to reopen the solution in VS2012 and that still seemed to work okay as well so it looks likes you can use SSDT database solutions in both VS2012 and VS2013 without any conversions or upgrades and you can pick and choose which version you want to use.

Automating DB Unit Tests in Visual Studio 2012 using TFS Build Definitions

A few months ago we upgraded our Database solution for DB Pro in VS 2010 to SSDT 2012.  Just before that I set up some database unit tests but these got forgotten about after the upgrade.

I’ve just been working on getting these back up and running so we can add new tests for the new work we’re going to be doing.  All our DB unit tests are in their own solution rather than being a project within our database solution.  This is because we run our tests as part of a TFS build and the build always complained unless the project was in it’s own solution.  I simply opened this DB unit test solution and it was automatically upgraded.

In 2010 we had a Test List file that grouped the tests into different lists and we just had to tell the build definition which tests from which lists to run.

However, in 2012 the Test Lists are deprecated and we now have to use Test Categories (although they are still supported if you do still want to use them – see below).  There is no Test Category file to replace the Test List file and we need to specify the category or categories in the method declaration for the test.  In the example below I’m assigning the test Scribe_Log_ServiceBrokerETLQueueSend to the categories ETL and ETL_StoredProcedures.

DB Unit Test Categories

Now we just need to configure a new Build Definition in TFS.  In the Process tab choose the Default Template and in the Projects to Build select your DB Unit Test solution.

Now click the ellipses on the Automated Tests row and a new window should open.  Click on Add and the Add/Edit Test Run window will open.  I’m going to use the Visual Studio Test Runner but if you do still want to use your Test List file (.vsmdi) you can use the MSTest Test Metadata File Test Runner.  You can leave the General tab with the default values or updated as you require but on the Criteria tab simply enter TestCategory=ETL as the Test case filter, obviously replacing “ETL” with the name of your Test Category.

You can also use the Test Category if you choose the MSTest.exe Runner and in this case simply enter the name of the Test Category in the Test category criteria text box on the Criteria tab.

Discarding changesets in TFS

We’ve recently updated our branching strategy at work and now have a Main branch and up to six branches which will each usually only have one work item on.  Once a work item is released to live the branches are forward merged from Main.  More recently I upgrade the database solution in Main and all the branches to SSDT for VS2012.  Each branch was manually upgraded so the changeset that contained the upgrade on Main wasn’t required to be forward merged into the branches. You can use the following command to discard any unwanted changesets so they don’t appear in the list of changesets in the merge wizard in TFS.

tf merge "$/sourcePath/Main/Data" "$/targetPath/Branches/BranchA/Data" /discard /recursive /version:C20144~C20666

tf.exe can be found in C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE If you have a batch of changesets that you want to discard, view them in the merge wizard in TFS and get the start and the end changeset numbers.  These are the two values you use in the /version tag prefixed with a “C”.  If there is only one changeset you still need to specific it as a range, e.g. /version:C20144~C20144. This seems to check out all the files that you would be merging if you weren’t discarding the changesets and you’ll need to check them in.

Upgrading from Visual Studio 2010 DB Pro (AKA Data dude) to Visual Studio 2012 SSDT (SQL Server Data Tools)

I’m currently working on a project that is replacing a suite of legacy applications (SQL Server 2008 R2 and ASP.NET) with a REST API with an underlying SQL Server 2008 R2 database.  The developers working on the API are already using Visual Studio 2012 and the DB team (of which I am a part) are looking to upgrade our Visual Studio 2010 database projects to use SQL Server Data Tools (SSDT) in Visual Studio 2012.

I’ve been playing about with it and this is what I’ve found so far.  Please note, this is still a work in progress and we have not yet completed the upgrade.

To make the upgrade process as easy as possible it was suggested that all the members of the DB team deploy SSDT for Visual Studio 2010.  This would allow people to choose whether they want to use VS 2010 or 2012 once the solution had been upgraded.  The Dec 2012 release of SSDT for VS 2010 can be found here:

Once I’d installed SSDT for 2010 the next time I opened our DB solution (in VS 2010) the “Convert To SQL Server Database Project” wizard was displayed.  There were two options available: “Yes, convert to SQL Server Database Projects” and “No, do not convert”.  As I was not ready to do the conversion I chose “No”.  Please note that there is a note under this option that says “You will not be prompted to convert these projects again”.  I was able to use the solution in 2010 as before.

I thought I’d play about with the conversion wizard so I took a copy of our development branch and opened it in VS 2012.  As I’d already said “No” to the upgrade I wasn’t shown the upgrade wizard window again but I was shown a window that had a list of the existing DB projects with a checkbox asking which ones I wanted to convert.  I converted them all and the solution opened in VS 2012.

Doing this resulted in new .sqlproj files being created to replace the original .dbproj files, although the .dbproj files still exist.  I also found that a <configName>.publish.xml file was created in each of the databases for each of the configurations that we have in our project (more on these files further on).

I also noticed that there was no “Schema View”.  In SSDT you now get more in the “SQL Server Object Explorer” window.  You’ll see you have two nodes: “SQL Server” which is used to connect to various SQL Servers; and “Projects” which replaces “Schema View” and shows the objects in each of the projects in the solution.  In “SQL Server” you will see a connection to “(localdb)”.  This is a SQL Server Express instance (I believe) and whenever you do a debug build and publish (or hit F5) the solution will get deployed to this instance.  This allows you to do stuff on an instance of SQL Server without having to install the full version of SQL Server.

Now I thought I’d try and build the solution against my local machine (SQL Server 2008 R2 instance) and the first problem I found was that in our 2010 solution we have .dbschema files which are included as database references in some of the DB projects.  Some of these .dbschema files were for some of our legacy databases but as SSDT can’t read .dbschema files I was getting lots of “unresolved reference” errors.

The .dbschema files have been replaced by .dacpac files in SSDT.  You can create a .dacpac file from an existing .dbschema file by following the steps in this blog: My legacy databases are still in use so I could just connect to it in “SQL Server Object Explorer” in VS 2012, right-click and “Create New Project”. This create a new SSDT project for my legacy database.  

I then attempted to create the .dacpac file using the “snapshot”.  This builds the project first but when doing that I got lots more “unresolved reference” errors.  I added the referenced legacy databases as projects to the solution and created new database references to the other database projects.  One thing to note is that you must make sure that the “Database Location” settings on the “Add Database Reference” box are entered correctly.  By default it will assume you are using a database variable to reference objects from other databases.  Our SQL objects are not reference in the project by using variables so I needed to delete the text from the “Database variable” box.  You need to ensure that the “Example usage” string matches how you reference objects from other databases in your database code.

Now once I’d added my dacpac as a database reference the solution was just hanging on the “Analyzing Database Schema…” part, and it won’t perform a build until this completes.  This is apparently a bug that was fixed in the SQL Server Data-Tier Application Framework (March 2013) patch.  I’m on a 64-bit machine so I just installed all 6 .msi files.  This fixed the problem and the build started.

I was then getting more “unresolved reference” errors, this time any time a system table was referenced, e.g.  “…has an unresolved reference to object [sys].[objects]”.

To fix this I created a new database reference on the project and selected the “master” database in the “System database” drop down box.

It turns out the references to the legacy databases are no longer required so I removed them leaving just my “master” DB references.

The schema checking in SSDT seems slightly tighter than in VS 2010 so I had to fix a couple of errors to do with data type mismatch in foreign key constraints but once this was done the solution built with no errors.

Now I wanted to check that I can still deploy the solution to my local DB.  SSDT uses the term “publish” rather than “deploy” and from what I’ve seen so far you can only run the publish at the database level, not the solution.

The <configName>.publish.xml files are the things that control the publishing.  (You can right-click the project and select “Publish” but this just open a blank “Publish Database” window that you’ll need to fill in).  The .publish.xml holds the data used to populate the “Publish Database” window meaning you don’t have to complete it each time, and these can be passed in as parameters to MSBuild.

I double-clicked on the .publish.xml file for the configuration for my local machine and the “Publish Database” window was opened.  I clicked on “Edit” and set the connection to my local instance.  I then clicked “Save Profile” which wrote the connection to my local machine to the .publish.xml file.  The next time I opened it, the connection string was there and I just had to click “Publish” and the database was created/updated on my local machine.

It seems you just need to create a .publish.xml file in each SSDT project for each of the environments you want to publish to.  More details on the .publish.xml files can be found in part 2 of the following blog:

I am currently looking at automatically publishing to our different environments using MSBuild.  In the meantime here’s a bit of information from people who have done the same:

Because I have installed SSDT for VS 2010 I’m able to open the converted solution in 2010 and 2012 at the same time and can use the .publish.xml files in either 2010 or 2012 to deploy to my local machine.

So far it seems a relatively painless process but I’ll see how I get on playing with MSBuild as I’ve not really used it before.


There is now an August 2013 update for the SSDT in VS 2012