none
CLR function failing after .NET framework 4.5.1 RRS feed

  • Question

  • I am running SQL Server 2012 Standard on Windows 2008 R2.  Last night I installed a new .NET framework (4.5.1) then rebooted the server, and today we noticed that a CLR function in the utility database, dbo schema had stopped working.  When I try to script the function out using SSMS, I get the error:

    Msg 6517, Level 16, State 1, Procedure simon_import_clean_staging, Line 101 
    Failed to create AppDomain "utility.dbo[runtime].7". 
    Exception has been thrown by the target of an invocation.

    There are two entries in the system event log:

    .NET Runtime Optimization Service (clr_optimization_v4.0.30319_64) - 1>Failed to compile: System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 . Error code = 0x80131f06 
    .NET Runtime Optimization Service (clr_optimization_v4.0.30319_64) - 1>Failed to compile: System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 . Error code = 0x80070003 

    It appears that the new .NET version did not install correctly, or that maybe the CLR function is trying to use the more recent version of the .NET framework even though it was created under an older version, and the new one is not compatible, which would in itself be unexpected.

    I'm going to try restarting the system again tonight and see if I see the same errors.  Aside from that, can anyone any suggestions?

    Thanks in advance for any help you could offer.

    Friday, January 3, 2014 5:39 PM

Answers

  • Microsft.SqlServer.Types is a system assembly. It is present in every SQL Server database, and cannot be removed or updated.

    If you are using that assembly in your CLR code, you may need to recompile against the 11.0 version of the assembly.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, January 6, 2014 8:42 PM

All replies

  • 4.5.1 is not a new .NET Framework.  It is a service pack to .NET 4.0.  So it updated the CLR that SQL Server will use to run your code.

    Does your CLR assembly have any references outside of the "blessed list" that you had to add to the database using CREATE ASSEMBLY?  If so they will need to be re-added after the update (and any future update).

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, January 3, 2014 5:59 PM
  • UPDATE:

    I've tried dropping all the functions and the assembly and recreating the assembly, but I get the error:

    Msg 6517, Level 16, State 1, Line 1

    Failed to create AppDomain "utility.dbo[ddl].18". 
    Exception has been thrown by the target of an invocation.

    I'm using Visual Studio 2012, and I've tried rebuilding the project with "Target Framework" of both ".NET Framework 4" and ".NET Framework 4.5", and both throw the same error when I try create the assembly.

    These are the assemblies in the project:

    System;
    System.Collections.Generic;
    System.Linq;
    System.Text;
    System.Threading.Tasks;
    System.Data.SqlTypes;
    Microsoft.SqlServer.Server;
    System.Text.RegularExpressions;

    Friday, January 3, 2014 9:12 PM
  • So do you have any of those referenced .NET Framework assemblies loaded in your database?  If so, you'll need to update them. See:

    Supported .NET Framework Libraries

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, January 3, 2014 9:24 PM
  • It seems that System.Data.SqlTypes is the issue since I had created an assembly that contained my function in the database and some time later upgraded from SQL 2008 to 2012.  There is only one CLR function that depends on the assembly and it is rarely used.  Prior to discovering the issue with the function I installed the new .NET framework to support the Google APIs, and then discovered the issue with existing function, so I don't know exactly when it occurred.

    I thought that given the circumstances I would just drop the CLR function, drop my assembly, drop the System.Data.SqlTypes assembly, recreate the System.Data.SqlTypes assemby using the newest DDL, recompile the user assembly that contains the function, import the user DLL into the database and recreate the function.  I was able to get to the point of dropping the user function and assembly, but I can't drop the System.Data.SqlTypes assembly because it says it's a system assembly:

    Msg 6560, Level 16, State 2, Line 2
    Assembly "Microsoft.SqlServer.Types" is a system assembly.  This operation is permitted only with user assemblies.

    I also can't use ALTER:

    Msg 15151, Level 16, State 2, Line 1
    Cannot alter the assembly 'Microsoft.SqlServer.Types', because it does not exist or you do not have permission.

    Is there some way to get back to the starting state of no assemblies in the database so I can start over?  There does not seem to be a way to drop the Microsoft.SqlServer.Types without touching system tables directly.

    Monday, January 6, 2014 6:54 PM
  • Microsft.SqlServer.Types is a system assembly. It is present in every SQL Server database, and cannot be removed or updated.

    If you are using that assembly in your CLR code, you may need to recompile against the 11.0 version of the assembly.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, January 6, 2014 8:42 PM