Developing Managed Code and Executing Within Microsoft SQL Server 2000

(Page 1 of 5 )

This article explains how to develop .NET based components and access them using Microsoft SQL Server 2000.

---

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:

Now, let us move on to implementing the concept.

Next: Developing the .NET component >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: Developing Managed Code and Executing Within Microsoft SQL Server 2000

· Databases can be difficult to deal with; I hope you found this article on developing...   >>> Post your comment now!