Rod Paddock

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:

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.