Call a webservice from TSQL (Stored Procedure) using MSXML

by admin 22. December 2009 15: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

7/28/2010 2:28:35 AM #

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>

tunç Kıral Turkey

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading