none
Unmanaged code in SQL CLR

    Question

  • I understand there are two ways to run native/unmanaged code in SQL Server 2005 in a .NET stored procedure:
    1. using COM objects; or
    2. using imported DLL functions.

    The thing I'd really like to be able to do, is to use a .NET assembly that either:
    1. contains both managed and unmanaged code (i.e. C++); or
    2. uses a second assembly that contains both managed and unmanaged code.
    SQL Server doesn't allow me to do either, and from what I've read it just isn't possible - however, I can't find this information direct from Microsoft.
    I understand the need for marking such assemblies as unsafe, but I just can't understand why mixed assemblies are forbidden, when COM and P/Invoke are allowed from within managed assemblies!

    Could someone confirm this? (And maybe put me straight as to the 'why'?!)

    Thanks in advance,

    Graham
    Wednesday, July 11, 2007 1:32 PM

Answers

  • You can't load a mixed mode assembly that contains both managed and native code inside SQL Server.  The way SQL hosts the CLR bypasses the traditional native assembly loading that would be necessary to handle some of the requireds steps to load native assemblies.  If you try, you'll get the following error indicating the SQL only supports pure .NET assemblies:

     

    Msg 6544, Level 16, State 1, Line 1

    CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' is malformed or not a pure .NET assembly.

    Unverifiable PE Header/native stub.

     

    You can still call unmanaged code from within your SQL CLR assembly, however you will have to use a mechanism such as P/Invoke to do it.  (IJW is not supported).

     

    Steven

    Thursday, July 19, 2007 4:25 PM

All replies

  • You can't load a mixed mode assembly that contains both managed and native code inside SQL Server.  The way SQL hosts the CLR bypasses the traditional native assembly loading that would be necessary to handle some of the requireds steps to load native assemblies.  If you try, you'll get the following error indicating the SQL only supports pure .NET assemblies:

     

    Msg 6544, Level 16, State 1, Line 1

    CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' is malformed or not a pure .NET assembly.

    Unverifiable PE Header/native stub.

     

    You can still call unmanaged code from within your SQL CLR assembly, however you will have to use a mechanism such as P/Invoke to do it.  (IJW is not supported).

     

    Steven

    Thursday, July 19, 2007 4:25 PM
  • Hi,

     

    I am trying to do something similar.  I want to call a C# assembly from a sql stored proc (I have this working at the moment). 

     

    This C# assembly calls a COM DLL using an auto-generated Interop assembly.  The DLL wraps calls to C++ code that internally might call C code as well.  I dont really know exactly what this code does but am not in a position to re-write them.  My main concern is that these C++ functions might have memory leaks and so I am worried about letting them run on the Database Server.  In particular, I am worried that a large memory leak could bring down the database.

     

    Do you think this is a valid concern or will SQL Server be robust enough to handle any issues?  I feel comforable about using C# code after reading the following article, however, I am still worried about calling the unmanaged code:

     

    http://www.hedgate.net/pages/articles/understanding-clr-integration

     

    One option we are considering is exposing the C# assembly as a web service hosted on an application server and calling this from the stored procedure on the database server.  This would mitigate against any memory leaks bringing down the database server.

     

    Any comments on doing this?  I have read that calling a webservice from a stored proc can be a bit flakey... see comment below from this link (http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx)

     

    "hi,
    i created a Dll in c#.net, which is accessing a webservice. i am using that dll inside sql server 2005. in sql server i created a function inside which i am using this Dll, again that function is used by a stored procedure. now the problem is that this Dll is working fine but some time it crashes. i.e. some time it doesn't respond, sometime it takes so much long time to execute which we can't accept as it is in use for some Bill payment Services. Also sometime i recieve error like 'Could not Load Assembly...'. Also after Dropping these assemblies and Function and Re-registering them the problem gets removed for some time, but again after 1 or 2 day the same problem gets started. "


    Any ideas or comments would be greatly appreciated... Thanks!

    Sunday, July 22, 2007 10:32 AM
  • Once you delve into unmanaged code running inside the SQL Server process, all bets are off in terms of robustness and SQL Server's ability to handle any issues such as memory leaks.  If you're not fully confident in the quality of the unmanaged code and you care about the reliability of your server you should probably not be running it inprocess.  There are also significant security concerns running unmanaged code that youdo not fully trust.

     

    I think the comment you quote is an unrelated problem, as the c# code to access a webservice won't be unmanaged.  I'm not sure why that user was running into problems.

    Monday, July 23, 2007 4:41 PM
  • Hi,

     

    Thanks for the feedback... we have decided to move away from putting the unsafe assembly in the SQL CLR and are looking at alternative solutions... we were having oo many issues trying to call unmanaged code... we're looking at alternatives at the moment.

     

    Thanks again for your comments!

     

    Gavin

    Wednesday, July 25, 2007 2:00 AM
  • Using P/Invoke or any other such mechanism has siginificant overhead I presume? I have this one compute intensive routine which I call millions of times... so having this in native and then calling through P/Invoke will  not work right?
    Friday, October 24, 2008 10:51 PM
  • I'm not sure on the performance of P/Invoke, but COM Interop in a SQL CLR assembly is very bad. Our stored procedures originally called COM methods many thousands of times... but performance was abysmal, far worse than a standard .NET application using COM.

    We got around this by creating an unmanaged DLL that acted as a proxy between the stored procedures and the COM objects. We batched our data from the stored procedures and passed it to the DLL, which in turn used the COM objects. Therefore, just a handful of P/Invoke calls from the .NET code instead of many thousands of direct COM calls, and performance substantially improved. Maybe this same approach could work for your routine too?
    Saturday, October 25, 2008 5:21 PM
  • Graham,

    Another option you could consider is using a C++ .net assembly compiled to /clr:pure. This will allow you to mix managed & unmanaged code (like with IJW).

    The restrictions on the resulting dll, like not being able to export functions (can only expose managed functions), or be called by unmanaged functions really don't matter when you want to host in the SQL CLR.

    The only problem, and this is a big one, is that I think there is a bug in SQL Server 2005, that causes a ".NET Framework execution was aborted ..." when you try to call into the pure assembly. You can create the assembly, create the function, but the call fails. I've tried this on SP3.

    However, you CAN do this successfully on SQL Server 2008. I've not tested on the RTM, but it works on SP1.

    Hope this helps.

     

    Tuesday, March 30, 2010 10:32 AM