Accessing a COM component from the stored procedure

Have you ever thought about accessing a COM component from the SQL Server stored procedure? Well, I have thought about few years back only and since then this is my favorite question while interviewing people.

You may be also aware that .NET CLR can be now also hosted from the SQL Server 2005. Still some times you may require accessing a COM component quickly from the stored procedure especially if you are using SQL Server 2000.

Next question is how to do this? Microsoft SQL Server has lot of system stored procedures. Some of the stored procedures mentioned below can be used to create instance of an OLE component. Once you get the handle, you can also execute exposed methods to get the result. In case of errors, you can also find out the error details.

In short, following are the steps to do this:

• Initialize the DLL from SQL Server using the sp_OACreate method call.
• Call into the various methods or properties of the DLL using sp_OAMethod, sp_OAGetProperty and sp_OASetProperty calls.
• You can get all errors using the sp_OAGetErrorInfo stored procedure.
• Destroy the automation object using sp_OADestroy.

I know what you must be thinking now. Yes, you are right you can create a wrapper over .NET assembly and use it in the stored procedure.

Cheers,
Amol