KB 322884, SQL2000 and COM Interop

  • Thread starter Thread starter Oleg Ogurok
  • Start date Start date
O

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.
 
Oleg Ogurok said:
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
Click to expand...

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
 
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.
 
Oleg Ogurok said:
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?
Click to expand...

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
 
Back
Top