Access Web Service from a SQL Server 2005 Stored Procedure

Question

Hi everyone,

Currently I have a stored procedure that makes some updates to a table, and after it makes them, I need to call a web service.

The procedure uses a transaction, and I need to call the web service at the end of the transaction. If the web service call should fail, the transaction would get rolled back.

I need to know how do I add a SOAP web service reference to SQL and how do I call the service from the stored procedure.

Distributed transactions / Enterprise services are excluded for architectural reasons.

Later Edit: Also, CLR assemblies are excluded. CLR has been deactivated on the server i need to deploy the procedure.

Answer 1

This Simple-Talk tutorial shows you how to accomplish this.

You can also do this using COM but it requires the use of sp_OACreate which many DBAs won't let you run (it requires a configure option to be turned on)

Answer 2

If you have control over the data source that this web service is using (bound to) you may consider the option of adding this data source as linked server to your SQL 2005 server. This way you will be able to execute a SQL using OPENQUERY against this data source without accessing it through web service.