Post
Ask Question

How To Call A Web Service From SQL Server

Introduction

 
In this blog, I have shown the process of calling web services through a stored procedure in SQL Server database. Also, I have explained how to call a stored procedure with a SOAP Envelope.
 
Step 1
 
Create a stored procedure in your SQL Server.
  1. CREATE proc [dbo].[spHTTPRequest]     
  2.       @URI varchar(2000) = 'http://localhost:55253/',          
  3.       @methodName varchar(50) = 'Get',     
  4.       @requestBody varchar(8000) = '',     
  5.       @SoapAction varchar(255),     
  6.       @UserName nvarchar(100), -- Domain\UserName or UserName     
  7.       @Password nvarchar(100),     
  8.       @responseText varchar(8000) output    
  9. as    
  10. SET NOCOUNT ON    
  11. IF    @methodName = ''    
  12. BEGIN    
  13.       select FailPoint = 'Method Name must be set'    
  14.       return    
  15. END    
  16. set   @responseText = 'FAILED'    
  17. DECLARE @objectID int    
  18. DECLARE @hResult int    
  19. DECLARE @source varchar(255), @desc varchar(255)     
  20. EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT    
  21. IF @hResult <> 0     
  22. BEGIN    
  23.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  24.       SELECT      hResult = convert(varbinary(4), @hResult),     
  25.                   source = @source,     
  26.                   description = @desc,     
  27.                   FailPoint = 'Create failed',     
  28.                   MedthodName = @methodName     
  29.       goto destroy     
  30.       return    
  31. END    
  32. -- open the destination URI with Specified method     
  33. EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password    
  34. IF @hResult <> 0     
  35. BEGIN    
  36.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  37.       SELECT      hResult = convert(varbinary(4), @hResult),     
  38.             source = @source,     
  39.             description = @desc,     
  40.             FailPoint = 'Open failed',     
  41.             MedthodName = @methodName     
  42.       goto destroy     
  43.       return    
  44. END    
  45. -- set request headers     
  46. EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'    
  47. IF @hResult <> 0     
  48. BEGIN    
  49.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  50.       SELECT      hResult = convert(varbinary(4), @hResult),     
  51.             source = @source,     
  52.             description = @desc,     
  53.             FailPoint = 'SetRequestHeader failed',     
  54.             MedthodName = @methodName     
  55.       goto destroy     
  56.       return    
  57. END    
  58. -- set soap action     
  59. EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction     
  60. IF @hResult <> 0     
  61. BEGIN    
  62.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  63.       SELECT      hResult = convert(varbinary(4), @hResult),     
  64.             source = @source,     
  65.             description = @desc,     
  66.             FailPoint = 'SetRequestHeader failed',     
  67.             MedthodName = @methodName     
  68.       goto destroy     
  69.       return    
  70. END    
  71. declare @len int    
  72. set @len = len(@requestBody)     
  73. EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len     
  74. IF @hResult <> 0     
  75. BEGIN    
  76.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  77.       SELECT      hResult = convert(varbinary(4), @hResult),     
  78.             source = @source,     
  79.             description = @desc,     
  80.             FailPoint = 'SetRequestHeader failed',     
  81.             MedthodName = @methodName     
  82.       goto destroy     
  83.       return    
  84. END    
  85. /*    
  86. -- if you have headers in a table called RequestHeader you can go through them with this    
  87. DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)    
  88. DECLARE RequestHeader CURSOR   
  89. LOCAL FAST_FORWARD    
  90. FOR   
  91.       SELECT      HeaderKey, HeaderValue    
  92.       FROM RequestHeaders    
  93.       WHERE       Method = @methodName    
  94. OPEN RequestHeader    
  95. FETCH NEXT FROM RequestHeader    
  96. INTO @HeaderKey, @HeaderValue    
  97. WHILE @@FETCH_STATUS = 0    
  98. BEGIN   
  99.       --select @HeaderKey, @HeaderValue, @methodName    
  100.       EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue    
  101.       IF @hResult <> 0    
  102.       BEGIN   
  103.             EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
  104.             SELECT      hResult = convert(varbinary(4), @hResult),    
  105.                   source = @source,    
  106.                   description = @desc,    
  107.                   FailPoint = 'SetRequestHeader failed',    
  108.                   MedthodName = @methodName    
  109.             goto destroy    
  110.             return   
  111.       END   
  112.       FETCH NEXT FROM RequestHeader    
  113.       INTO @HeaderKey, @HeaderValue    
  114. END   
  115. CLOSE RequestHeader    
  116. DEALLOCATE RequestHeader    
  117. */     
  118. -- send the request     
  119. EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody     
  120. IF    @hResult <> 0     
  121. BEGIN    
  122.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  123.       SELECT      hResult = convert(varbinary(4), @hResult),     
  124.             source = @source,     
  125.             description = @desc,     
  126.             FailPoint = 'Send failed',     
  127.             MedthodName = @methodName     
  128.       goto destroy     
  129.       return    
  130. END    
  131. declare @statusText varchar(1000), @status varchar(1000)     
  132. -- Get status text     
  133. exec sp_OAGetProperty @objectID, 'StatusText', @statusText out    
  134. exec sp_OAGetProperty @objectID, 'Status', @status out    
  135. select @status, @statusText, @methodName     
  136. -- Get response text     
  137. exec sp_OAGetProperty @objectID, 'responseText', @responseText out    
  138. IF @hResult <> 0     
  139. BEGIN    
  140.       EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT    
  141.       SELECT      hResult = convert(varbinary(4), @hResult),     
  142.             source = @source,     
  143.             description = @desc,     
  144.             FailPoint = 'ResponseText failed',     
  145.             MedthodName = @methodName     
  146.       goto destroy     
  147.       return    
  148. END    
  149. destroy:     
  150.       exec sp_OADestroy @objectID     
  151. SET NOCOUNT OFF    
  152.      
  153. GO    
The Stored Procedure takes the following parameters.
  1. @URI: the URI of the web service
  2. @MethodName: this would be ‘GET’ or ‘POST’
  3. @RequestBody: this is the SOAP xml that you want to send
  4. @SoapAction: this the operation that you want to call on your service
  5. @UserName: NT UserName if your web service requires authentication
  6. @Password: the password if using NT Authentication on the web service
  7. @ResponseText: this is an out parameter that contains the response from the web service
Step 2
 
Make the setting in SQL for it.
  1. Use master  
  2. sp_configure 'show advanced options', 1   
  3.   
  4. GO   
  5. RECONFIGURE;   
  6. GO   
  7. sp_configure 'Ole Automation Procedures', 1   
  8. GO   
  9. RECONFIGURE;   
  10. GO   
  11. sp_configure 'show advanced options', 1   
  12. GO   
  13. RECONFIGURE;  
Step 3

Call the stored procedure (Here is a sample call to my service).

  1. declare @xmlOut varchar(8000)  
  2. Declare @RequestText as varchar(8000);  
  3. set @RequestText=  
  4. '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">  
  5.    <soapenv:Header/>  
  6.    <soapenv:Body>  
  7.       <tem:CreateOrder>  
  8.          <!--Optional:-->  
  9.          <tem:OrderRequest>  
  10.             <tem:OrderId>200</tem:OrderId>  
  11.             <!--Optional:-->  
  12.             <tem:OrderName>something</tem:OrderName>  
  13.          </tem:OrderRequest>  
  14.       </tem:CreateOrder>  
  15.    </soapenv:Body>  
  16. </soapenv:Envelope>'  
  17. exec spHTTPRequest  
  18. 'http://localhost/testwebservices/helloworldservice.asmx',  
  19. 'POST',  
  20. @RequestText,  
  21. 'http://tempuri.org/CreateOrderForMe',   -- this is your SOAPAction:  
  22. '', '', @xmlOut out  
  23. select @xmlOut   
Make sure your SOAP action is correct. Copy this action from your services. It will show up when your service is RUN.
 

Conclusion 

 
In this blog, I have explained how to call a web service from the stored procedure in SQL.
Next Recommended Reading How To Call A Function In SQL Server Stored procedure