Webservice From SQL

Question

Is it possible to call a remote web service from a stored procedure and use the values ​​that are set?

Answer 1

If you are using SQL 2005/2008, you can do this from the CLR stored procedure if you have the option to install and run. For more information:

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

Answer 2

Service Broker can provide the kind of functionality you are looking for here.

Answer 3

AntiSanta, CLR . , . , - . //, , -. , -, WS-.

, , -.

Answer 4

In SQL Server 2000 and later (if the CLR is not enabled), you can use COM through stored procedures ( sp_OACreate, sp_OAMethodetc.) if you have an existing COM shell for your web service.

Answer 5

Here is my code that works.

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT  
if @hr < 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed',16,1) 
return end  
exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @UrlString, false  
if @hr <0 begin set @msg = 'sp_OAMethod Open failed' goto eh end  
exec @hr = sp_OAMethod @obj, 'send'  
if @hr <0 begin set @msg = 'sp_OAMethod Send failed' goto eh end  
exec @hr = sp_OAGetProperty @obj, 'status', @status OUT  
if @hr <0 begin set @msg = 'sp_OAMethod read status failed' goto eh end  
if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +str(@status) goto eh end  
exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT  
if @hr <0 begin set @msg = 'sp_OAMethod read response failed' goto eh end  
exec @hr = sp_OADestroy @obj  
select @response  

......

eh:

exec @hr = sp_OADestroy @obj  
Raiserror(@msg, 16, 1)  
Return