SQL Server 2008 and COM server programming

Question

Hi,

We need to access a custom ATL COM server from SQL server 2008. Ideally we want the COM server to live on a separate machine since it is part of a larger software application. I know of the COM/OLE automation stored procedures that SQL provides (sp_OAXXXX) but they don't seem to accept a computer/server name for remote invocation.

Is it possible to acheive this using C# based CLR stored procedures? Are there any other approaches? We also have a java wrapper for the COM server so remote batch files is an option? I am looking to hear from anyone who may have dealt with a similar problem.

Thanks, Sam

Answer 1

On the sp_OACreate track, you can bundle your COM server into COM+ on the remote server, and then export a proxy MSI, which you can then install on your SQL Server (also into COM+, and configure the Remote Server info). This way you can ease the horrors of DCOM.

But as per the other posters, calling remote COM servers from SQL would be a last resort.

Answer 2

Here's one approach: you could create a SOAP web service, using either WCF or asmx, to wrap the COM server interface(s). Create a CLR assembly and add a web reference for the web service to the assembly. You can then call methods on the COM server as web service calls. You'll need to build and run your SQLCLR assembly with EXTERNAL_ACCESS permissions to do this. The advantage of this approach is that is pretty straightforward and it will be easy to set up a test frame for the web service independently.