Calling a .Net application via a COM wrapper in SQL Server 2005

I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
the option COM Interop set to True. Once built, the VS2003 project solution
produces a .tlb file. A setup is produced (from within VS2003) that includes
the dll and tlb files. These are installed on a PC (running on Windows
2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (with
the latest SQL Server 2000 Service Pack). A trigger fired from one of the
tables in our own database (within SQL Server 2000) makes the calls to the
COM wrapped assembly.
CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
FOR INSERT, UPDATE,DELETE
AS
DECLARE @.hr int
DECLARE @.factory int
DECLARE @.xalert int
DECLARE @.response int
DECLARE @.errMsg varchar(8000)
DECLARE @.success bit
EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUTPUT
select @.hr
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.factory , @.hr
RETURN
END
EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
@.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactory',
'http://localhost:8080/axis/services/xalertsport'
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.factory , @.hr
RETURN
END
EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
'MyXalerts55_030506_ss', 1100
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.xalert , @.hr
RETURN
END
EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.response , @.hr
RETURN
END
IF (@.success = 0)
BEGIN
EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
IF (@.hr <> 0 )
BEGIN
EXEC sp_displayoaerrorinfo @.response , @.hr
RETURN
END
END
EXEC @.hr = sp_OADestroy @.xalert
EXEC @.hr = sp_OADestroy @.response
EXEC @.hr = sp_OADestroy @.factory
This works fine and we are able to create the objects and make the method
calls on it.
We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
VS2005, using the same settings as before. A new setup was created and
installed on a PC running on Windows 2000 Server and has SQL Server 2005 SP1
installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
database and our own databse have been made "trustworthy". The assembly
created above (with .net 2,.0) has been created within SQL Server, setting
External_acesss (tried unsafe as well) permissions on it.
We are using the same trigger as above and the same code within the trigger.
The trigger fails on the sp_oacreate line with the following error.
OLE Automation Error
HRESULT: 0x80131700
Source: ODSOLE EXtended Procedure
We have tried to put more COM Wrapper around the generated tlb , but that
has not worked - giving the same error. Using .net 1.1 assembly (same one as
used for SQLServer 2000) , we get the same error. However, It is possible to
create other standard COM objects (like the SQLDMO objects), using the
sp_oacreate. So, it may be something particular to .net assemblies being
called (via the COM interop wrapper) from SQL Server 2005.You should really go another route. Having SQL Server execute code hosted by CLR was never supported
though spOACreate nor xp_cmdshell. I'd consider re-writing that com object as a CLR stored procedure
and you are in a supported environment. Below is my general "canned" response:
It is not supported for extended stored procedures or sp_OA procedures to call .NET code in CLR;
hosted within SQL Server's adress space..
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
Also, below is witnh permission from David Browne, explaining how you can have SQL Server execute
CLR code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last week, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:EC73FFA6-3F3E-47EE-809E-18BF1D60A88E@.microsoft.com...
>I have a .Net 1.1 assembly (written using C# in VS2003). This assembly has
> the option COM Interop set to True. Once built, the VS2003 project solution
> produces a .tlb file. A setup is produced (from within VS2003) that includes
> the dll and tlb files. These are installed on a PC (running on Windows
> 2000/2003 server/Windows XP Pro) that has SQL Server 2000 running on it (with
> the latest SQL Server 2000 Service Pack). A trigger fired from one of the
> tables in our own database (within SQL Server 2000) makes the calls to the
> COM wrapped assembly.
> CREATE TRIGGER [UserProfile_Trigger1] ON [UserProfile]
> FOR INSERT, UPDATE,DELETE
> AS
> DECLARE @.hr int
> DECLARE @.factory int
> DECLARE @.xalert int
> DECLARE @.response int
> DECLARE @.errMsg varchar(8000)
> DECLARE @.success bit
> EXEC @.hr = sp_OACreate 'csSOAPClient2.CXalertsApiSoapClient', @.factory OUTPUT
> select @.hr
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.factory, 'getXalertsInstanceUsingSOAPDefaults',
> @.xalert OUTPUT, 'rmi://localhost:50001/com/xalert/server/api/XAlertsFactory',
> 'http://localhost:8080/axis/services/xalertsport'
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.factory , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.xalert, 'queueByID', @.response OUTPUT, 1, '',
> 'MyXalerts55_030506_ss', 1100
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.xalert , @.hr
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.response, 'Success', @.success OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> IF (@.success = 0)
> BEGIN
> EXEC @.hr = sp_OAGetProperty @.response, 'ErrorText', @.errMsg OUT
> IF (@.hr <> 0 )
> BEGIN
> EXEC sp_displayoaerrorinfo @.response , @.hr
> RETURN
> END
> END
> EXEC @.hr = sp_OADestroy @.xalert
> EXEC @.hr = sp_OADestroy @.response
> EXEC @.hr = sp_OADestroy @.factory
>
> This works fine and we are able to create the objects and make the method
> calls on it.
> We have now taken that .Net 1.1 project and recompiled it in .Net 2.0 and
> VS2005, using the same settings as before. A new setup was created and
> installed on a PC running on Windows 2000 Server and has SQL Server 2005 SP1
> installed. "CLR integration" and "xp_cmdshell" have been enabled. Master
> database and our own databse have been made "trustworthy". The assembly
> created above (with .net 2,.0) has been created within SQL Server, setting
> External_acesss (tried unsafe as well) permissions on it.
> We are using the same trigger as above and the same code within the trigger.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
>
> We have tried to put more COM Wrapper around the generated tlb , but that
> has not worked - giving the same error. Using .net 1.1 assembly (same one as
> used for SQLServer 2000) , we get the same error. However, It is possible to
> create other standard COM objects (like the SQLDMO objects), using the
> sp_oacreate. So, it may be something particular to .net assemblies being
> called (via the COM interop wrapper) from SQL Server 2005.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> You should really go another route. Having SQL Server execute code hosted
> by CLR was never supported though spOACreate nor xp_cmdshell. I'd consider
> re-writing that com object as a CLR stored procedure and you are in a
> supported environment. Below is my general "canned" response:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's adress space..
> See:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
> Also, below is witnh permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
>
Thanks,
But if I understand the question, the OP has compiled and installed the
assembly in SQL CLR. If so, he just needs to issue write a CLR stored
procedure in .NET that implements the trigger logic. Accessing the assembly
the OLE Automation stored procedures is no longer necessary. The trigger
body just needs to invoke a CLR stored procedure.
David|||Yes, I fully agree. However, the first post indicates that this isn't what the OP had in mind. Or
perhaps the first post seemed a bit ambiguous on this:
> We are using the same trigger as above and the same code within the trigger.
> The trigger fails on the sp_oacreate line with the following error.
> OLE Automation Error
> HRESULT: 0x80131700
> Source: ODSOLE EXtended Procedure
Thanks for making that option explicit, though. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:%23Hf3w3dmGHA.4536@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$J%23mTUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
>> You should really go another route. Having SQL Server execute code hosted by CLR was never
>> supported though spOACreate nor xp_cmdshell. I'd consider re-writing that com object as a CLR
>> stored procedure and you are in a supported environment. Below is my general "canned" response:
>> It is not supported for extended stored procedures or sp_OA procedures to call .NET code in CLR;
>> hosted within SQL Server's adress space..
>> See:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;322884
>> Also, below is witnh permission from David Browne, explaining how you can have SQL Server execute
>> CLR code executing in its own process:
> Thanks,
> But if I understand the question, the OP has compiled and installed the assembly in SQL CLR. If
> so, he just needs to issue write a CLR stored procedure in .NET that implements the trigger logic.
> Accessing the assembly the OLE Automation stored procedures is no longer necessary. The trigger
> body just needs to invoke a CLR stored procedure.
> David