Rod Paddock

Sponsors

The Lounge

Wicked Cool Jobs

Advertisement

Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Accessing External Assemblies from SQL Server CLR Stored Procedures

Accessing External Assemblies from SQL Server CLR Stored Procedures

In the last couple of weeks I have been working on a specialized set of CLR Stored procedures for communicating with a mainframe via an ODBC driver. Each CLR stored proc will send an SQL query to the server and then dump the results of that query into a SQL Server table. There will be around 50 CLR stored procedures used to do this.

During this process I created an abstract class. This abstract class would be the basis for each CLR stored procedure. Because of the nature of CLR stored procedures (they are all shared/static functions) I had to create a new class for each mainframe call and then instantiate that from a separate CLR stored proc.

The code is like this:

 

<Microsoft.SqlServer.Server.SqlProcedure()> _      
Public Shared Sub CardholderNamesCLR(ByVal cAcctNo As String)

Dim oTest As New CardHolderNamesLib             

oTest.cAcctNo = cAcctNo             

oTest.RunProc()     

End Sub

 

From the code you can see that I create an instance of the CardHolderNamesLib class which inherits from my abstract class. This was all fine and good when all my concrete classes and abstract class existed in the same assembly.

So after creating two or three of these classes the client came back to me and said: “We want to put each class and CLR stored proc in its own assembly. Note I said that my abstract and concrete classes existed in the same assembly.

To a .NET programmer this would be simple. Just create new assembly and reference it from each CLR stored proc assembly. No problem right? Well… I wouldn’t be writing this post if it was that easy.

So I created an external assembly… Went to my CLR stored procedure project and tried adding my assembly. Right clicked on my project…selected Add Reference and…. The list was rather limited:

 Add Ref Dialog Part1

As you can see there’s no real way to add a reference to an external assembly.

Well I did what all good developers do. I hit Google and after consulting numerous blogs and MSDN pages there I found no answer to my question.

Well today I found my answer…by accident actually but I found it….

After deploying an assembly to my SQL Server I returned to my project and went to add a reference to one of the built in assemblies and what did I see. The assembly I had just deployed was there in the reference list.  Bingo! Now I could add a reference to an “external” assembly. The only requirement is the “external” assembly must be “pre-deployed” to the server.

So I created a new SQL Server project from Visual Studio, added my abstract class and deployed that assembly to the server. Then I returned to my CLR stored procedure, went to the Add Reference dialog and there it was my assembly was included in the list of assemblies.

Add CLR Reference Part 2

After this it was pretty simple to refactor all my code using the abstract class contained in my "external" assembly. The funny thing about this was the total lack of posts about this subject. I guess the concept of abstract classes, concrete classes and inheritance has not made it far into the SQL Server world.

  

 

crossposted from blog.dashpoint.com

Posted Thu, Sep 13 2007 3:31 PM by Rod Paddock [MVP]

[Advertisement]

Comments

Anonymous wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Fri, Sep 14 2007 6:01 AM

Didn't you click on the Projects tab?

Rod Paddock [MVP] wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Fri, Sep 14 2007 8:18 AM

Hi Anon :)

 I went back and tried that this morning. It does work as well. I think in my initial forays into this I must have missed that. It actually does the same thing but in one step instead ot 2. Thanks for the other idea.

Anonymous wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Fri, Sep 14 2007 9:19 AM

Hi,

Yes because when you deploy using VS, it will automatically deploy dependent assemblies also.

btw, my name is Anonymous. My parents couldn't agree on a name so they called me that. :) Seriously though, for reasons of National Security, I cannot reveal my identity...

oh I crack me up

:)

Rod Paddock [MVP] wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Fri, Sep 14 2007 10:40 AM

Hi Anon again :)

 After a little more work this morning (nothing better to do at 5:30am I suppose :))

I added the existing project to another CLR stored procedure project. When I deployed the 2nd CLR solution it generated a deployment error because CLR Project 1 had a dependancy on the helper library.

The solution did deploy CLR library #2 successfully though.  I think I like the idea of deploying the library assembly first as a seperate deployment and relying on an already installed assembly.

Now I begin to wonder.... How do I deploy a new version of the helper library without uninstalling all of the dependant assembies. Time to look at the ALTER ASSEMBLY command to see what it says.

And anon you can rest easy Mr Gonzalez's last day is today :)

Anonymous wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Fri, Sep 14 2007 4:09 PM

Weird. I just tested it again and it works for me (I'm using VS2008 beta 2). Are both your projects SQL Server Projects?

Mike wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Mon, Sep 17 2007 2:12 PM

Okay, now I have an assembly that I purchased from a vendor which has some special parsing functionality. The assembly is typically included in a program but I want to use it in a CLR stored procedure but can't see any way to do that. Is this a limitation of the SQL Server CLR procedures? Thanks

Rod Paddock [MVP] wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Tue, Sep 18 2007 9:28 AM

Yo Anon :)

 My comment was that it does work. But... if I try and use the assembly in a completely seperate solution I get deployment errors for that project saying that another assembly is dependant on the original assembly.

I did look into ALTER ASSEMBLY and it appears that it will allow online changes to an assenbly with some restrictions.

Rod Paddock [MVP] wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Tue, Sep 18 2007 9:30 AM

Hi Mike

 It may be a limitation of CLR stored procs.  I am pretty sure you can deploy your assembly to the server using the CREATE ASSEMBLY command. This may or may not work depending on what dependencies your assembly has.

Is this a spendy library or something that has a 30 day eval. I could take a shot.

The Daily Grind 1230 at aoortic! dot com wrote The Daily Grind 1230 at aoortic! dot com
on Tue, Sep 18 2007 10:11 PM

Pingback from  The Daily Grind 1230 at  aoortic! dot com

guidmaster´s .NET blog wrote links for 2007-09-22
on Sat, Sep 22 2007 7:21 AM

Accessing External Assemblies from SQL Server CLR Stored Procedures - Rod Paddock Accessing External

guidmaster´s .NET blog wrote links for 2007-09-22
on Sat, Sep 22 2007 7:21 AM

Accessing External Assemblies from SQL Server CLR Stored Procedures - Rod Paddock Accessing External

http://scrum.codebetter.com/blogs/rodpaddock/archive/2007/09/13/accessing-external-assemblies-from-sql-server-clr-stored-procedures.aspx wrote http://scrum.codebetter.com/blogs/rodpaddock/archive/2007/09/13/accessing-external-assemblies-from-sql-server-clr-stored-procedures.aspx
on Wed, Apr 9 2008 11:55 AM
Simon wrote re: Accessing External Assemblies from SQL Server CLR Stored Procedures
on Fri, Oct 31 2008 5:03 AM

Great, helped a lot - thanks for sharing!

Devlicio.us