Moving to a new version of SQL Server is always interesting, and this version is proving to be no exception. It has been a lot of fun so far, and I hardly know where to start, but I will start with CLR assemblies.

My first migration goal is to bring my data mart (3 databases) over to my 2012 development server, run the SSIS packages, build a cube, and query it. To migrate the databases I backed up the 2008 R2 versions, restored them to 2012, and then set the compatibility level of each to 110.

Things seemed to go smoothly, for a while. The SSIS migration was straightforward, and the packages ran without a lot of fuss. The cube migration seemed to be going well too, until I actually tried to build it. At that point, however, I started seeing “binding errors” in one of the views that sources one of the dimensions.

After identifying the view, I tried running it. The result was an error message something like

Msg 6517, Level 16, State 1, Procedure vwDimSite, Line 10
Failed to create AppDomain “master.sys[runtime].7”.
Exception has been thrown by the target of an invocation.

The fun had begun. Googling didn’t help much, other than pointing me to a CLR function issue. Examining the view itself, I spotted three columns that used built-in geospatial functions. Since that was the only CLR code in the entire view, I tried removing those columns and sure enough, it worked. But why?

I took a wild guess. I knew I had added a few CLR assemblies of my own to some of my databases, and I confirmed that SQLSpatialTools was installed on this one. The next step was to remove it and try the view again. Except that I couldn’t remove it. I couldn’t run the remove script that I had for the assembly, which drops the functions and then drops the assembly, and I couldn’t drop anything directly using SSMS. I was stuck.

I decided to re-migrate the database, adding the CLR assembly ‘remove’ script to the process, but after restoring to SQL Server 2012 and before setting the new compatibility level. I then ran the view in question again and it worked! Of course there is still a stored procedure somewhere in that database that is referencing the now non-existent CLR functions, but that is the least of my problems at the moment.

Obviously, custom CLR functions are not my specialty. I have never written one myself. It would appear, however, that the custom assembly in this database was incompatible with SQL Server 2012, and that that in turn made it impossible to use any CLR functions, not even those that were built in.

In retrospect, I probably could have just set the compatibility level back to 105 (SQL Server 2008 R2), run the ‘remove’ script, and returned the compatibility level to 110. I’ll have to try that on another database that uses a custom CLR assembly.

Update:

Tried dropping an assembly from a different DB and it worked just fine. It must have been that particular assembly. Except I tried the same thing I did earlier, same DB, same assembly (but on a different server) and it worked too.

Update:

There is a fair amount of weirdness in this new release, as you might expect. What I have seen so far is mostly in the developer tools, though, as was the case with 2008 R2. In a future post I will summarize what I have seen so far. It’s a good product, though, and I like it!