KB 322884, SQL2000 and COM Interop

[Oleg Ogurok]

Hi all,

I've just discovered the following article on MSKB saying that SQL Server
2000 doesn't support calling .NET stored procedures via SP_OA* methods.
http://support.microsoft.com/?kbid=322884

I'm a little puzzled. I've been using this method for serveral months with
no problems.
Basically, I have a .NET class library exposed to COM. I call this library's
methods from either SQL's stored procedures or triggers. Here's the code
that calls it:

---------------
DECLARE @Object int
EXEC sp_OACreate 'MyDotNetLib.Class1', @Object OUT
PRINT @Object
EXEC sp_OAMethod @Object, 'MethodName, NULL, @param1
EXEC sp_OADestroy @Object
---------------

So far this has been working. I'm wondering if at some point this code is
going to crash SQL server or cause other problems.

Any idea?

Thanks,
-Oleg.

 

[David Browne]

Hi all,

I've just discovered the following article on MSKB saying that SQL Server
2000 doesn't support calling .NET stored procedures via SP_OA* methods.
http://support.microsoft.com/?kbid=322884

I'm a little puzzled. I've been using this method for serveral months with
no problems.
Basically, I have a .NET class library exposed to COM. I call this
library's methods from either SQL's stored procedures or triggers. Here's
the code that calls it:

---------------
DECLARE @object int
EXEC sp_OACreate 'MyDotNetLib.Class1', @object OUT
PRINT @object
EXEC sp_OAMethod @object, 'MethodName, NULL, @param1
EXEC sp_OADestroy @object

I don't know if it will crash SQL server, but there is an easy workaround.

Take the .tlb file for your assembly and install it in COM+ as a server
application. Then the CLR will be hosted in a COM+ process and only an
unmanaged proxy will be loaded in the SQL Server process.

David

 

[Oleg Ogurok]

David,

I haven't worked with COM+. Could you please point me to any docs on how to
register a DLL as COM+ server? Is it any different from what Visual Studio
..NET 2003 does for you automatically when you set "Register for COM interop"
in the project properties?

-Oleg.

 

[David Browne]

David,

I haven't worked with COM+. Could you please point me to any docs on how
to register a DLL as COM+ server? Is it any different from what Visual
Studio .NET 2003 does for you automatically when you set "Register for COM
interop" in the project properties?

Sure.

http://www.microsoft.com/resources/...ndard/proddocs/en-us/comexp/adinstal_4acz.asp

The easiest thing to do is to run
regasm myassembly.dll /codebase /tlb:myassembly.tlb
This will register your assembly for COM interop and generate a type library
file.

Next to install the component as a COM+ server application, open up the
Component Services MMC console from

Control Panel
Administrative Tools
Component Services
COM+ Applications
Right Click
New
Application

Create an empty server application, name it and configure its credentials.
Then just drag your .tlb file into the components folder of your new
application. This will change the COM registration for your component so
that instead of loading the CLR client applications will have their
interface calls marshalled to a COM+ process, which will load the CLR and
host your component.

David