[dba-SQLServer] Calling a Webservice...

Well I know it can be done, using WSE 2.0 (3.0 is out) and extended 
stored procedures.
I believe doing it this way may cause security headaches. But if you are 
just returning one variable
from an internal site maybe okay.

These guys have a Beta ESP they are trying to sell, it is attempting to 
be generic and requires a dll
WebService Extended Stored Procedure for SQL Server 2000
http://www.rdlcomponents.com/EXSP/default.aspx

I believe there maybe an easier way in SQL Server 2005 with the CLR

Also you could try using SQLXML and SOAP


: Using Extended Stored Procedures or SP_OA Stored Procedures to Load
CLR in SQL Server Is Not Supported
 http://support.microsoft.com/default.aspx?scid=kb;en-us;322884


This may work for you.

If you want to hit a webservice, you should use the sp_oa* stored 
procedures
to invoke MSXML2.XMLServerHTTP, and communicate with the webservice that
way.

Make sure MSXML parser version 3 or greater is installed on your SQLServer
server (it's installed with IE6 so if you have that, you're good to go).


In sql create the following stored procedure.


create procedure http_get( @sUrl varchar(200), @response varchar(8000) out)
As


Declare
  @obj   int
 , at hr   int
 , at status int
 , at msg varchar(255)


   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', @sUrl, 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


   return
eh:
  exec @hr = sp_OADestroy @obj
  Raiserror(@msg, 16, 1)
  return


go


then run this


declare @resp varchar(8000)
exec http_get
  
'http://[webserver]/[virtualDirectory]/HelloWorl­d.asmx/Hello?Name=david',
  @resp out


print @resp




Francisco Tapia wrote:

>In the middle of a design discussion for a system that is in development, it 
>was brought up if I could just call a webservice from a stored procedure. 
>That's my real question can it? Initially I figured that the website for 
>this system would take an order in, and submit the order to sql server, an 
>inhouse person would review the order and upon it's approval click the 
>approve button and allow the order to be submitted to the enterprise 
>database. Because the Sales Order number needs to be stored on the sql 
>server for refernce, I figured it would be easiest to have the inhouse 
>application call the stored procedure, and let the server go and talk to the 
>webservice and retreive the "Sales Order". If this cannot be done, then I'll 
>need to take all the data at the client side submit it and take the 
>resulting Sales Order and submit it to the sql server, giving it plenty of 
>room for errors due to network interuptions (rare I know, but sometimes not 
>quite)... Has anyone ever had to do this?
>
>Thanks,
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada