Last post May 10, 2013 07:34 AM by zoggling
May 08, 2013 11:56 AM|zoggling|LINK
We are looking to send XML requests and receive responses back via XML to/from internal and external web services, and would like to do this via stored procedures in SQL Server. The following link is one way to achieve this:
However, since we are running SQL Server 2012, it would be useful to know if there are any other potential ways of achieving this.
Any suggestions would be greatly appreciated.
May 10, 2013 02:35 AM|Amy Peng - MSFT|LINK
Please try to see if the follow can help:
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object
The link your provide is for the Sql Server 2000.
But for the Sql Server 2005 or above, the CLR is one of the best way to invoke your web service.
#How to call Web Service from a SQLCLR Stored Procedure:
Hope it can help you.
May 10, 2013 05:26 AM|zoggling|LINK
Thank you for your response. Do you happen to know why SQLCLR is the best method for SQL Server 2005 onwards? It seems far more involved than just a few lines in a stored procedure that you highlighted in your first option. What are the advantages of the
May 10, 2013 06:04 AM|Amy Peng - MSFT|LINK
Not CLR, but the SQL CLR Stored Procedure.
People disguess the advantage of in here:
#CLR Stored Procedure Calling External Web Service
May 10, 2013 07:34 AM|zoggling|LINK
Hi Amy, thanks for this. These posts mostly state advantages, but I have seen quite a few disadvantages mentioned at the following:
I think its likely we will be heading down the OLE Automation procedures route at present. I actually found the following to be particularly helpful in our case: