Call a webservice from TSQL (Stored Procedure) using MSXML

by Vishal 22. December 2009 10:13

I am working on a n integration project that required me to call a webservice through a stored procedure in the SQL database. After proving my concept using the CLR integration features in Sql Server, I learned that the production database was actually running on Sql Server 2000 compatibility level. There went my proof of concept.

 

So now I have to resort to doing web service calls the old way, making post requests using MSXML. This is what my stored procedure looks like. 

CREATE proc [dbo].[spHTTPRequest] 
      @URI varchar(2000) = '',      
      @methodName varchar(50) = '', 
      @requestBody varchar(8000) = '', 
      @SoapAction varchar(255), 
      @UserName nvarchar(100), -- Domain\UserName or UserName 
      @Password nvarchar(100), 
      @responseText varchar(8000) output 
as 
SET NOCOUNT ON 
IF    @methodName = '' 
BEGIN 
      select FailPoint = 'Method Name must be set' 
      return 
END 
set   @responseText = 'FAILED' 
DECLARE @objectID int 
DECLARE @hResult int 
DECLARE @source varchar(255), @desc varchar(255) 
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'Create failed', 
                  MedthodName = @methodName 
      goto destroy 
      return 
END 
-- open the destination URI with Specified method 
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Open failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
-- set request headers 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8' 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
-- set soap action 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
declare @len int 
set @len = len(@requestBody) 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
/* 
-- if you have headers in a table called RequestHeader you can go through them with this 
DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500) 
DECLARE RequestHeader CURSOR 
LOCAL FAST_FORWARD 
FOR 
      SELECT      HeaderKey, HeaderValue 
      FROM RequestHeaders 
      WHERE       Method = @methodName 
OPEN RequestHeader 
FETCH NEXT FROM RequestHeader 
INTO @HeaderKey, @HeaderValue 
WHILE @@FETCH_STATUS = 0 
BEGIN 
      --select @HeaderKey, @HeaderValue, @methodName 
      EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue 
      IF @hResult <> 0 
      BEGIN 
            EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
            SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'SetRequestHeader failed', 
                  MedthodName = @methodName 
            goto destroy 
            return 
      END 
      FETCH NEXT FROM RequestHeader 
      INTO @HeaderKey, @HeaderValue 
END 
CLOSE RequestHeader 
DEALLOCATE RequestHeader 
*/ 
-- send the request 
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 
IF    @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Send failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
declare @statusText varchar(1000), @status varchar(1000) 
-- Get status text 
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out 
exec sp_OAGetProperty @objectID, 'Status', @status out 
select @status, @statusText, @methodName 
-- Get response text 
exec sp_OAGetProperty @objectID, 'responseText', @responseText out 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'ResponseText failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
destroy: 
      exec sp_OADestroy @objectID 
SET NOCOUNT OFF 

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 your 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

 

Here is a sample call to my service 

declare @xmlOut varchar(8000)
Declare @RequestText as varchar(8000);
set @RequestText=
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
   <soapenv:Header/>
   <soapenv:Body>
      <tem:CreateOrder>
         <!--Optional:-->
         <tem:OrderRequest>
            <tem:OrderId>200</tem:OrderId>
            <!--Optional:-->
            <tem:OrderName>something</tem:OrderName>
         </tem:OrderRequest>
      </tem:CreateOrder>
   </soapenv:Body>
</soapenv:Envelope>'
exec spHTTPRequest 
'http://localhost/testwebservices/helloworldservice.asmx', 
'POST', 
@RequestText,
'http://tempuri.org/CreateOrderForMe',
'', '', @xmlOut out
select @xmlOut 

The stored procedure runs and selects the response from my service call which in my case is:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <CreateOrderForMeResponse xmlns="http://tempuri.org/">
   <CreateOrderForMeResult>
    <Message>Order Created for me</Message>
   </CreateOrderForMeResult>
  </CreateOrderForMeResponse>
 </soap:Body>
</soap:Envelope>

Note that the stored procedure automatically adds the appropriate http headers for Content-Type, SoapAction and Content-Length. If you need to pass additional http headers with your request, refer the section in the center of the stored procedure which is commented out. Basically you need a table in your database called RequestHeaders with 3 columns; HeaderKey, HeaderValue, Method. Populate this table with the keys and values for the additional headers. The Method column will contain GET or POST depending on what method you are using.

Another thing to note here is that I tried the above calls with MSXML2.XMLHTTP and Microsoft.XMLHTTP but did not succeed with either. I kept getting an error from msxml3.dll or msxml6.dll with message "The parameter is incorrect". I believe this to be a bug with msxml that requires the request body text to be in a specific format that is not available through a sql data type and hence cannot be used via TSQL. THese will hoever work fine when performing a simple http request (non- SOAP) or a SOAP request that does not need a request body. for performing SOAP requests from within TSQL the only component that worked for me was MSXML2.ServerXMLHTTP. There are some third-party components that I came across, some paid, but MSXML2.ServerXMLHTTP worked just fine for me.

The next step is to actually take the SOAP response that I recieved and extract the appropriate data from it - that will be another post.

Tags: , , ,

.NET | C# | SQL | SQL Server Management Studio

Comments (22) -

tun&#231; Kıral
tunç Kıral
7/27/2010 9:28:35 PM #

thx dude you are awsome....
this is perfect.
But there is one condition must added for complete.

while coding web service this must added to web.comfig file.

    <webServices>
      <protocols>
        <add name="HttpPost"/>
        <add name="HttpGet"/>        
      </protocols>      
    </webServices>

Ashely
Ashely United States
4/19/2011 8:08:33 AM #

Thanks for the code.  And thank you tunc for the tip for the comfig file.

David
David Mexico
9/9/2011 3:40:49 AM #

This code works perfect. Thanks for sharing this!

Caner
Caner Turkey
12/2/2011 4:24:36 AM #

This code works perfect. Thanks for sharing

kishore
kishore United States
4/15/2012 11:31:32 PM #

working Great! thanks

ymao
ymao Canada
7/23/2012 3:42:30 AM #

Hi there,

Does this code work with SQL 2008?

Adrian
Adrian Netherlands
8/2/2012 2:00:36 AM #

Thank you very much! Your article and codes are very helpful! Smile

Nauman
Nauman
9/3/2012 11:15:25 PM #

how can i call a simple service with json variables int t-sql

api.sendsms.pk/sendsms/YOUR-SECRET-API-KEY.json Accepts three parameters by HTTP POST request (phone = phone number to which the sms will be sent, msg = message, type = 0 for regular and 1 for flash sms) to send sms.

Carl Rochlitz
Carl Rochlitz Belgium
9/10/2012 9:40:18 PM #

This code works perfect. Thanks for sharing!

Parthiv
Parthiv United States
10/17/2012 12:38:00 PM #

Superb!! I was using other method to post my XML data from T-SQL but somehow it didn't work. I used code from this page and modified it to my need and it worked perfect!!!

Thank you so much for sharing this code. This is immense help!

Martin
Martin Czech Republic
3/14/2013 2:37:56 AM #

Hello, How I can call method with parameters? I use  myService.svc and it has method SetPassword(string userName, string password).

Fouzia
Fouzia Canada
3/18/2013 6:25:39 AM #

thank you very much, your code helped me a lot. il work in SQL server 2008.

Will Spurgeon
Will Spurgeon United States
5/2/2013 9:29:52 AM #

Dude, this works great, thank you very much.

Manju
Manju India
6/20/2013 5:57:58 PM #

Thank you very much... exactly what I was looking for!!

J Phillips
J Phillips United States
6/21/2013 10:57:19 AM #

This is awesome. Thanks. Is there a way to increase the timeout?

I have a long running process that is triggering a timout error.

Pulkit Gulati
Pulkit Gulati India
7/14/2013 10:35:50 PM #

I am getting a send failed using this code. Can anybody please tell me what could be the reason for this?

shailendra
shailendra India
7/21/2013 8:42:41 PM #

I just need to pass data to web service using soap call.

will i need to change in script  OR can i use this with only by passing valid parameters to this proc......

shailendra
shailendra India
7/21/2013 9:57:41 PM #

its awesome example.

there is no need to change in script just pass valid parameter and execute store procedure.

thank you.....

Reza Khan
Reza Khan United Kingdom
7/22/2013 10:57:47 PM #

hello my friend , this is great code. Is there are a way of accessing a RestFUL web service in TSQl ?

Kindest regards,

Reza

Daud Jan
Daud Jan United States
8/17/2013 3:35:12 AM #

Hello everyone,

I am using this query for calling a webservice but it returns me the following error in response xml.
"Server was unable to process request. Data at the root level is invalid. Line 2, position 1."

Any help in this issue will greatly appreciated.
Kind Regards,
Daud.

Brad
Brad New Zealand
8/22/2013 9:18:10 AM #

This code was magic for getting me started - however I was affected by the varchar(8000) limit.
Here is an adjusted stored procedure that returns an xml type and has no character limit (that I have been able to hit)



GO
/****** Object:  StoredProcedure [dbo].[spHTTPRequest_xml]    Script Date: 08/23/2013 08:52:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:    Brad Old - Bold Tech Solutions Ltd - www.bold.co.nz
-- Create date: 01/08/2013
-- Description:  www.vishalseth.com/.../...dure%29-using-MSXML.aspx
-- =============================================
ALTER procedure [dbo].[spHTTPRequest_xml]
      @URI varchar(2000) = '',      
      @methodName varchar(50) = '',
      @requestBody varchar(8000) = '',
      @SoapAction varchar(255),
      @UserName nvarchar(100), -- Domain\UserName or UserName
      @Password nvarchar(100),
      @responsexml xml output
as
SET NOCOUNT ON
declare @responseText varchar(8000)
IF    @methodName = ''
BEGIN
      select FailPoint = 'Method Name must be set'
      return
END
set   @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
DECLARE @t table (ID int, strxml xml)


EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
                  source = @source,
                  description = @desc,
                  FailPoint = 'Create failed',
                  MedthodName = @methodName
      goto destroy
      return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Open failed',
            MedthodName = @methodName
      goto destroy
      return
END
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF    @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Send failed',
            MedthodName = @methodName
      goto destroy
      return
END
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status, @statusText, @methodName
-- Get response text
Insert into @t (strxml)
     exec sp_OAGetProperty @objectID, 'responseText' --, @responseText out
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'ResponseText failed',
            MedthodName = @methodName
      goto destroy
      return
END
select @responsexml =  strxml from @t
destroy:
      exec sp_OADestroy @objectID
SET NOCOUNT OFF



Luis
Luis Argentina
9/27/2013 4:22:28 AM #

great piece of code, very handy.

for those asking about the timeout issue, doing some googling I've found a method called "setTimeouts".

so I've added this portion of code to the SP, right before the "OPEN" command is invoked:

--set the timeout, receive timeout to 5 minutes
EXEC @hResult = sp_OAMethod @objectID, 'setTimeouts', null, 5000, 5000, 30000, 300000
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Set timeout failed',
            MedthodName = @methodName
      goto destroy
      return
END

Hope this helps.

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading