A downloadable file for this article is available here.
The sample downloadable solution (zip) is entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition. But I am confident that it would work with other versions of Windows (which support .NET 1.1) versions as well.
Why is it necessary and how can we achieve it?
In general, if it is related to database related transactions or manipulations, we can easily achieve it using T-SQL (Transact SQL). By developing our own stored procedure, we can even improve the performance. But there could be situations where we may need the help of some external routines, which may not be available in SQL Server 2000. Examples include pinging to another computer, reading a text file, and so on.
T-SQL, on the other hand, is basically designed only to interact with a database; it is not intended to develop any of the external routines (which are outside the scope of the database). But, in real scenarios, we may have some of our external routines execute from within the SQL Server 2000 environment.
The Microsoft SQL Server team thought about these kinds of situations and created support for accessing any COM component from within the SQL Server 2000 environment. But one should also understand that Microsoft might not (or does not) provide support for any failures in such cases. If you have guts and if you really test them well before taking it to the production environment, there is no doubt that accessing external routines from within SQL Server 2000 would greatly help programmers.
Another issue to consider is that Microsoft SQL Server 2000 doesn’t know anything about .NET. In fact, it doesn’t even know that the CLR, COM+ or other technologies exist within .NET. It knows how to access external routines only using COM. There exists no concept like “managed” from SQL Server 2000 point of view. Then how can we set up our .NET managed code to get accessed from SQL Server 2000?
As usual, we develop our managed code (.NET code) using a “class library” project, sign it “strongly” and push it into GAC (Global Access Cache). Since Microsoft SQL Server 2000 doesn’t know anything about GAC, we need to register it as a COM component also (using “RegAsm” Utility). Once all of the above steps are completed, we can use any of the following “extended stored procedures” in SQL Server 2000, to access the component:
Since this is a very simple demonstration, I will try to create a simple .NET component with only one method, which accepts two integer values as parameters and returns their sum.
You can start opening your Visual Studio and proceed through the following steps:
Go to File -> New -> Project.
Select “Class Library” as the Project Template and specify the “Name” of the project as “MySample”. You will be presented with a default class, “Class1.”
Within the “Solution Explorer”, right click on “Class1” and delete it.
Again within the “Solution Explorer”, right click on the project, go to Add -> Add Class.
Give the name of the class as “ISample” and modify the code as follows:
Public Interface ISample
Function getSum(ByVal a As Integer, ByVal b As Integer) As Integer
End Interface
Again within the “Solution Explorer”, right click on the project, go to Add -> Add Class.
Give the name of the class as “CSample” and modify the code as follows:
Public Class CSample
Implements ISample
Public Function getSum(ByVal a As Integer, ByVal b As Integer) As Integer Implements ISample.getSum
Return a + b
End Function
End Class
Again within the “Solution Explorer”, double click on “AssemblyInfo.vb” and add the following line at the bottom
<Assembly: AssemblyKeyFile("..\..\MySample.snk")>
Go to Start -> Programs -> Microsoft Visual Studio.NET 2003 -> Visual Studio.NET tools -> Visual Studio.NET 2003 Command Prompt.
Go to the path of your project folder (which is “MySample”), using the Command Prompt.
Type the following command to provide a strong name to the component. The strong name key pair is necessary for registering the component with GAC cache.
Sn –k MySample.snk
Now build your solution by pressing Ctrl+Shift+B. It should complete successfully. If you receive any errors, doublecheck the path of “MySample.snk”. It should be available in the same project folder (and not in “bin” or any other sub folder).
In the previous section, you finished developing the component. Now you need to register the COM component and also deploy it into GAC cache. Proceed with the following steps to do this:
If you didn’t open your Visual Studio command prompt, do it by going to Start -> Programs -> Microsoft Visual Studio.NET 2003 -> Visual Studio.NET tools -> Visual Studio.NET 2003 Command Prompt.
Go to the “bin” folder of your project folder (which is “MySample”), using the command prompt.
Type the following command to register the assembly as COM component
Regasm /tlb:MySample MySample.dll
After receiving a successful message, again type the following command to get the component registered within GAC cache.
gacutil /I MySample.dll
The screen should look something like the following (Fig1):
That completes your deployment.
In case you want to unregister the component (say you may want to modify the code after successful registration), you can use the following commands (separately) to do the same (from the same bin folder):
Just press F5 to execute the script and it should give you a result with a value of 30. You can modify the above code with better error handling as follows:
DECLARE @ExecutionResult INT, @errorSource VARCHAR(100), @errorDescription VARCHAR(100), @result VARCHAR(200),@hnd INT
All the previous sections focused on how to execute the .NET component. But they didn't really explain the concept behind it. Let us go through some of the most important technical issues.
First of all, you can observe that I created an interface something like the following:
Public Interface ISample
Function getSum(ByVal a As Integer, ByVal b As Integer) As Integer
End Interface
Is the interface really necessary (as I am working with only a single method)? Even though it is not logically necessary, it is technically necessary. The COM architecture is designed with the beginning point as the “interface” itself. So, it is the most suggested way to start, when we deal with COM components.
The method within the interface is being “implemented” in the class “CSample” as follows:
Public Class CSample
Implements ISample
Public Function getSum(ByVal a As Integer, ByVal b As Integer) As Integer Implements ISample.getSum
Return a + b
End Function
End Class
You should observe the ones which are highlighted in red. That gives you a clear idea of how to implement the above-defined interface.
After completing everything, I used the following “extended stored procedures” to deal with external processes:
Sp_OACreate
Sp_OADestroy
Sp_OAGetErrorInfo
Sp_OAMethod
“Sp_OACreate” is specially used to create an object, based on an existing COM component. “Sp_OADestroy” does just the opposite. It removes the COM object created by “Sp_OAcreate” from the memory (freeing the resources). Before destroying the COM object, we need to work with the COM object. We can execute any method of the COM object using “Sp_OAMethod”. If any problem (or failure) occurs (during interaction with the COM component), we can fetch the error information using “Sp_OAGetErrorInfo”. I didn’t use “sp_OASetProperty” as the class “CSample” doesn’t have any properties to be set.
Remarks
This article focused mainly on SQL Server 2000 and not at all on SQL Server 2005 (or SQL Server Yukon). It is not at all suggested to use this type of implementation in SQL Server 2005 as it has its own CLR implementation within the SQL Server engine itself. You can directly use .NET programming within SQL Server 2005 without referring to (or working with) any COM related issues.
I am trying to contribute few more real scenarios using the same concept. Keep watching for the new articles. Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
Visual Basic for Beginners
