locked
CLR proc assembly registration issue RRS feed

  • Question

  • Have a .NET 3.5 Sql Clr SP that uses RabbitMQ nuget package. Apparently RabbitMQ has a dependency on system.servicemodel, version=3.0.0.0 as I get this error when trying to register my custom clr assembly with sql:

    Msg 10301, Level 16, State 1, Line 21
    Assembly 'SqlClrRabbitMq' references assembly 'system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
    Msg 6528, Level 16, State 1, Procedure PublishMessages, Line 26
    Assembly 'SqlClrRabbitMq' was not found in the SQL catalog of database 'FOTNConfig'.

    system.servicemodel is a GAC assembly so why is SQL complaining?

    scott

    Thursday, April 9, 2015 8:58 PM

Answers

  • The short answer to "how do I change this policy" is "you don't". SQL Server is very particular about which versions of system assemblies are allowed and even prohibits some system assemblies from being cataloged to the database at all, even when specified as permission set=unsafe. This is by design, to ensure the security/reliability/scalability of the database.

    WCF (or items referencing or dependencies on WCF) doesn't work with SQL Server 2012 or above. There have been a number of related posts on this specific topic in this forum.

    Hope this helps, Bob

    • Marked as answer by scott_m Thursday, April 9, 2015 10:20 PM
    Thursday, April 9, 2015 10:17 PM
  • Ugh, I am now stuck using Sql ServiceBroker instead of RabbitMQ.

    Here is the Microsoft Connect issue if anyone else wants to petition M$FT to reconsider System.ServiceModel support in SQL CLR:

    Link


    • Edited by scott_m Thursday, April 9, 2015 10:42 PM
    • Marked as answer by scott_m Thursday, April 9, 2015 10:42 PM
    Thursday, April 9, 2015 10:20 PM

All replies

  • Did you check this path below?

    • Add reference.
    • Browse, "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll
    • Click OK

    Ricardo Lacerda

    Thursday, April 9, 2015 9:19 PM
  • Ricardo, I tried adding a reference to System.ServiceModel.dll and rebuilding, redeploying and re-registering assembly.  When registering my custom assembly, I get this error:

    Msg 10301, Level 16, State 1, Line 22
    Assembly 'SqlClrRabbitMq' references assembly 'system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
    Msg 6528, Level 16, State 1, Procedure PublishMessages, Line 27
    Assembly 'SqlClrRabbitMq' was not found in the SQL catalog of database 'FOTNConfig'.

    The assembly is targeting .NET 3.5.  SQL Server is 2014.

    I tried manually copying System.ServiceModel.dll to C:\SqlClr and I get a diff error message when registering my custom CLR assembly:

    Warning: The Microsoft .NET Framework assembly 'system.servicemodel, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
    Msg 6586, Level 16, State 1, Line 24
    Assembly 'System.ServiceModel' could not be installed because existing policy would keep it from being used.

    I guess the next question is how do you change the policy?


    • Edited by scott_m Thursday, April 9, 2015 9:36 PM
    Thursday, April 9, 2015 9:25 PM
  • Ok scott_m, 

    Drag the assembly ServiceModel.dll into GAC manually (copy). Do that into all the database's instances that's processing the CLR function, stored procedures and so on.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.


    Ricardo Lacerda



    Thursday, April 9, 2015 9:43 PM
  • Ricardo, I checked and System.ServiceModel 3.0 is already in the GAC on the SQL Server 2014 instance.  

    Thursday, April 9, 2015 9:51 PM
  • The short answer to "how do I change this policy" is "you don't". SQL Server is very particular about which versions of system assemblies are allowed and even prohibits some system assemblies from being cataloged to the database at all, even when specified as permission set=unsafe. This is by design, to ensure the security/reliability/scalability of the database.

    WCF (or items referencing or dependencies on WCF) doesn't work with SQL Server 2012 or above. There have been a number of related posts on this specific topic in this forum.

    Hope this helps, Bob

    • Marked as answer by scott_m Thursday, April 9, 2015 10:20 PM
    Thursday, April 9, 2015 10:17 PM
  • Ugh, I am now stuck using Sql ServiceBroker instead of RabbitMQ.

    Here is the Microsoft Connect issue if anyone else wants to petition M$FT to reconsider System.ServiceModel support in SQL CLR:

    Link


    • Edited by scott_m Thursday, April 9, 2015 10:42 PM
    • Marked as answer by scott_m Thursday, April 9, 2015 10:42 PM
    Thursday, April 9, 2015 10:20 PM
  • What you can do is to re-build the client without the reference to the ServiceModel.dll (i.e. if you do not need the wcf capabilities). If I remember correctly there is only one place - apart from the wcf "stuff) where you would need to do any changes in the source, and that is where the client code used a SyncObject property of a type. I replaced that with a pure object which I locked on. Then just re-build and create the assembly. Works 100's! We are using it in over 100 production databases.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Friday, April 10, 2015 3:47 AM