Call a web service from SQL CLR?

Question

I have a SQL Server 2012 stored procedure that returns a table. I need to modify this SP to add an extra value to the returned table. Unfortunately, this added value comes from a web service call. From my research, I collect the main ways to do this using the OLE Automation procedures (sp_OA ...) in SQL or the SQLCLR stored procedure. Given the security context in which the sp_OA ... procedures are executed, the only return value is the VARCHAR (10) registration key, and the service is accessed severally (ten to twenty per hour), I assume that the SQLCLR method is the way to go. In addition, the web service is hosted on our intranet and is not available to the outside world.

Is there a better way to achieve what I need? Better Value Better, Better Security, Simplified Code, and Support

Answer 1

Please do not use sp_OA* OLE automation sp_OA* . They do not seem to have been officially deprecated, but SQLCLR replaces both OLE Automation procedures and extended stored procedures.

Yes, this can be done quite easily in SQLCLR. You can find examples of using WCF (as shown in @CodeCaster's answer) or using HttpWebRequest / HttpWebResponse (I have additional information in this answer: How to call a web service from a SQL Server stored procedure ). Also keep in mind that sometimes you also need to add a serialization assembly: Using web services and Xml serialization in CLR integration

Coding and maintenance
Web services provide a good API, but if you change the structure, you will have to recompile and redistribute at least some of this. Assuming that the exchange of information is quite simple, I am inclined to think that considering this as a standard web request adds more flexibility. You can create a general web request function (scalar or TVF) that takes parameters and a URI and creates a properly formatted XML request and sends it to the URI. Then it receives a response and simply returns the XML. This way you change a bit of responsibility, because now you need to parse the XML response and not get a good object. But XML is easy to parse in SQL Server, and you can reuse this function in any number of places. And, if the remote service is constantly being updated, updating the stored procedure to change the query string passed to the web service and / or changing the parsing of the XML response is a simple ALTER procedure and should be easily tested. There is no need to recompile / remodel the SQLCLR assembly.

Security
Regardless of how you need a “clean” web service call, the main thing is to be safe and not be lazy and turn TRUSTWORTHY ON (as also shown on the linked page from @CodeCaster's answer, and unfortunately, most of the other examples here are on intervets). The proper way to make this safe is to do the following:

• Sign your assembly
• In the [master] database, create an asymmetric key from the DLL of your assembly.
• In addition, in [master] create a login from this asymmetric key
• Provide a new login to the EXTERNAL ACCESS ASSEMBLY .
• Build your assembly with PERMISSION_SET EXTERNAL_ACCESS , not UNSAFE

Answer 2

You can definitely call the WCF service using the SQL CLR .

If you don't want this, you can write a Windows service in C # that tracks or inspects the table for changes . Depending on how you implement this service, the response to the new record will be close to immediate. See also How to notify a Windows service (C #) of a table table change (sql 2005)? .

Then you can make a service call with C #, do the required work, and save the result in a column.

If you need additional information, for example, additional variables obtained during the exchange, you can enter a new table to store it and the actual result that you are interested in. Then attach this table from the table in your question.