Adventures in autodeploy: SQL Server 2012, SSDT, and SQLCLR

I'm the kind of person that doesn't often/always use Visual Studio "autodeploy" (that is, the "Deploy Solution" option) for SQLCLR projects. It's always been missing things (like ALTER ASSEMBLY) and never quite kept up with the newer things you could do with SQLCLR (e.g. ordered table-valued functions, multiparameter aggregates). But every once-in-a-while, especially while I'm teaching and writing example assemblies on-the-fly, its a useful way to get things cataloged fast without writing DDL.

So when I installed SQL Server 2012 and the SQL Server Data Tools on top of an existing VS2010 installation, I was not entirely surprised that, when opening a SQLCLR project, I was prompted to "convert to an SSDT project or leave it alone". Either choice promised me that "I wouldn't be prompted again" but I didn't want that. So I simply cancelled out of the dialog.

However, VS2010 SQLCLR projects autodeploy *doesn't work* against a SQL2012 database. Ever.
Even with the simplest projects. A profiler trace of the proceedings looks like some of the
DDL it generates is incompatible with SQL Server 2012. Hrmph.

So now I choose the "convert" option, and expect to see a full-fledged SSDT project in
Solution Explorer after the conversion. I don't. Looks like I have approximately the same
project. Not so. Looking at the project properties gives me all the SSDT properties, and
using "Add/New Item" on the project gives the entire list of (85 or so) items that you can
add to an SSDT project. So I DO have a full-fledged SSDT project. Does not mean I'll have to
import the rest of the database objects to get "autodeploy" (there's no "Deploy Solution" but
there is a Publish… option) to work??

Let's try this on the pubs database (I can always re-create the pubs database if something
goes wrong). And "Publish" simply publishes the assembly, leaving the rest of the database
undisturbed. Turns out that is what I want, so…great.

Now, I've made the moral equivalent of a SQL Server 2012/VS2012 SQLCLR project by conversion.
How do I make a new one? There no choice with SSDT but "Other Languages/SQL Server/SQL Server
Database Project". Made an empty one of those. So let's make an assembly. Add/New Item…and
the SQLCLR items don't appear in the list of all new items. But there is a branch off the
tree for "SQL CLR" and "SQL CLR C#". The "SQL CLR" is VB.NET. So first, I need to add an
assembly info file, if I want one of these. So far, so good, but there are no referenced
assemblies other than .NET 4.0's System.Dll (the properties window, SQLCLR tab, show Assembly
Info and fill in the dialog does the same thing). It looks like it's smart enough to build a .NET 4.0 target because my target database in project properties is SQL Server 2012. Now let's add a SQLCLR UDF (the default
template for this no longer returns "Hello", but String.Empty, hmmm…). And I do get
System.Data and System.Xml added to the references. And Build and Publish works. BTW, during
the "Publish" process, you also have the ability the generate a publishing script rather than
publish in real-time. That's nice.

So, I'll go through of compare the generated DDL later and see if I can do anything in SQLCLR
2012 that SQLCLR 2008 autodeploy (Publish/Deploy) can't do. For now, I at least have a way to
replicate the functionality of SQLCLR projects. That's works with a SQL Server 2012 (and 2008
and 2005, though I didn't try each one, I'll take them at their word, for now) database. Whew! But, once you've converted the
original VS2010 project (unless you choose, "save old project" during conversion) you can't
go "home" anymore… You're in SSDT-project-land now.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.