Writing CLR Stored Procedures

  • Article

I'm trying to write some CLR sprocs to test out the TDS behavior of CLR sprocs versus T-SQL sprocs for TDS protocol documentation purposes.  If you are not aware, we now have the TDS protocol spec on MSDN for your bedtime reading: 

https://msdn2.microsoft.com/en-us/library/cc448435.aspx

Note I'm also finishing up a full blown netmon parser for TDS as part of the protocol documentation effort.  To ensure this works well I'm investigating the dusty corners of TDS protocol stream behavior to ensure we have it properly spec' d and my parser works too.

On to CLR sprocs topic.  First thing I did was crank up VS.NET 2008 and try to create a database project so I can upload some test CLR sprocs.  It doesn't work because VS.NET 2008 is smart and detects the target SQL Server is 2008 and not 2005 so it refuses to co-operate and create a nice little CLR project for me.  I suppose I could use a 2005 server but I'm lazy.

No worries, I know how to manually upload CLR sprocs with all the magic T-SQL incantations.

Next I do this:

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=SAFE
go

Msg 10301, Level 16, State 1, Line 1
Assembly 'TranStress' references assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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.

Note yes I am really really lazy and don't want to create a separate clean library project with my CLR stored proc in it.  I just want to upload my test client exe that also just happens to have the sproc class in it and get on with my life.  My other non-sproc test code happens to ref System.EnterpriseServices.  Arg.

Can I just shovel EnterpriseServices in?

create assembly EnterpriseServicesAssemblyForSQLServerWhoIsTooLazyToLookInGac from N'e:\WINNT\Microsoft.NET\Framework\v2.0.50727\System.EnterpriseServices.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS
go

Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.EnterpriseServices' failed because assembly 'System.DirectoryServices' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.DirectoryServices.DirectorySearcher::SetSearchPreferences][mdToken=0x60000f8][offset 0x0000050E][found address of value 'System.DirectoryServices.Interop.AdsSortKey'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection::RetrieveDirectorySynchronizationCookie][mdToken=0x60001c6][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection::RetrieveVLVResponse][mdToken=0x60001c7][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection+ResultsEnumerator::GetCurrentResult][mdToken=0x60001d4][offset 0x00000033][found address of Native Int] Expected numeric type on the stack.

Arg,  you don't have to tell me everything twice, work with me brother.  Ok, I didn't like System.EnterpriseServices anyway, it was highly over-rated, so I removed it from my other code (I wasn't using it anymore anyway).

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=SAFE
go

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'Main' on type 'TranStress.Program' in safe assembly 'TranStress' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Ok, ok, SQL, I know this game.  I'm starting to get offended.

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=UNSAFE
go

Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'TranStress' failed because assembly 'TranStress' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Ok, let me try this but it makes me feel strange:

alter database transtress set trustworthy on
go
create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=UNSAFE
go

Ahh!  Now that was easy.  Next, I have to expose my function as a stored proc.  I created my CLR proc like so:

class CLRTestJunk
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ClrProc1(string param1)
    {
        using(SqlConnection conn = new SqlConnection("Context Connection=true;"))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                int v = -1;
                int.TryParse(param1, out v);
                cmd.Parameters.Add("@p1", SqlDbType.Int);
                cmd.Parameters[0].Value = v;
                cmd.CommandText = "insert t1 (f1) values (@p1)";
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Next I run this:

create procedure ClrProc1 as external name ClrTestJunkAssembly.CLRTestJunk.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'CLRTestJunk' in assembly 'TranStress'.

Ok, now what?  I crank up reflector to see if it's there, yes it's there.

Maybe because I forgot to add the param:

create procedure ClrProc1(@param1 varchar(max)) as external name ClrTestJunkAssembly.CLRTestJunk.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'CLRTestJunk' in assembly 'TranStress'.

No. no. no.  I'm not beaten yet.  Go search around web for 6505, try this:

create procedure ClrProc1(@param1 varchar(max)) as external name [TranStress.ClrTestJunkAssembly].CLRTestJunk.ClrProc1
go

No dice.  Ok SQL CLR, you win, I strip out a separate library class with my code.  Same problem:

create assembly ClrClassLibraryAssembly from N'C:\Project\ADONet\ClrClassLibrary\bin\Debug\ClrClassLibrary.dll' WITH PERMISSION_SET=SAFE
go
create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.ClrClassLibrary.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'ClrClassLibrary' in assembly 'ClrClassLibrary'.

Ok, SQL CLR you are making me feel stupid, I thought I was a kick butt dba until today, now you are just mocking me, thanks a ton.  I found another newsgroup entry that gave me an idea:

create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6576, Level 16, State 4, Procedure ClrProc1, Line 1
Type 'ClrClassLibrary.ClrClassLibrary' in assembly 'ClrClassLibrary' is not public.

Ohhh!  I am getting closer.  Here is my class now for reference:

namespace ClrClassLibrary
{
    class ClrClassLibrary
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ClrProc1(string param1)
        {

To reference this in SQL Server I have to say ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1, not bloody intuitive but ok, I can guess why we did it this way (SQL Server's 4 part naming convention probably won out over CLR).

Ok, make my class public and away I go:

create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6550, Level 16, State 2, Procedure ClrProc1, Line 1
CREATE PROCEDURE failed because parameter counts do not match.

Ok, I forgot to add back my param:

create procedure ClrProc1(@param1 varchar(max)) as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6552, Level 16, State 3, Procedure ClrProc1, Line 1
CREATE PROCEDURE for "ClrProc1" failed because T-SQL and CLR types for parameter "@param1" do not match.

Ok, I think I have performed full code coverage for all CLR error messages by now, one more try, the winner is:

create procedure ClrProc1(@param1 nvarchar(max)) as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Yes!  Now to call my CLR stored procedure:

exec ClrProc1 N'123'

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Whoops.  Ok, need to enable CLR:

sp_configure 'clr enabled',1
go
reconfigure with override
go

Ok, one more time:

exec ClrProc1 N'123'

Works!  Looks like VS.NET does quite a bit of nasty CLR grunt work for me.  But overall what have we learned about CLR stored procedures?

  1. Make sure your class is public.
  2. Avoid references to CLR modules like System.EnterpriseServices, try to keep your code as reference free as possible.
  3. Avoid namespaces.
  4. Ensure you enable CLR on the server.
  5. Probably best to have it in it's own class library to keep things as simple as possible.
  6. Don't change versions of SQL Server or VS.NET will no longer work.
  7. Always learn how to do it using T-SQL versus rely on VS.NET if you want it to work anyway.

You can always sniff the TSQL that VS.NET uses to configure your CLR sproc using SQL Profiler and save this.