Webservice From SQL

Question

Can I call a remote webservice from a Stored Procedure and use the values that areretuned?

Answer 1

If you're using SQL 2005/2008, you could do this from a CLR stored procedure if you have the ability to install and run these. For more info:

http://msdn.microsoft.com/en-us/library/ms190790.aspx

Answer 2

Service Broker might provide the sort of functionality you're looking for here.

Answer 3

As The AntiSanta says, using a CLR stored procedure this is possible. The real question is whether you can avoid it altogether. It feels upside-down to call a web service from a stored procedure. Ideally you'd have some other service/app/layer that calls both the stored procedure and a web service. Possibly the stored proc returns parameter values for the web service, and you commit your local transaction after the WS call is complete.

This would make both debugging, deployment and support much more simple in the long run, and decouples the direct reference between the stored proc and the web service.

Answer 4

On SQL Server 2000 and up (if CLR is not enabled), you can use COM through stored procedures (sp_OACreate, sp_OAMethod, etc) if you have an existing COM wrapper for your web service.

Answer 5

A Good artical on how to call a webservice from SQL:

http://blog.hoegaerden.be/2008/11/11/calling-a-web-service-from-sql-server-2005/