Call SOAP Web Service From MS SQL

Hi Guys,

I was given a task to call a SOAP web service from MS SQL. There few steps which are required before writing the T-SQL for invoking web service.


Step 1:

Give rights to invoke OA(Object Access) for SOAP. Below is how to do it

sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO

Step 2:

TSQL stored procedure which calls the web service. I Assume that you have a web service with WebMethod "GetProgram" with one Parameter "Code". I am also assuming that its a POST method. Below is the strored procedure:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Ritesh Tandon>
-- Create date: <01-Jun-2015>
-- Description:    <Invoke Web Service From SQL Stored Procedure>
-- =============================================
Alter PROCEDURE pCallWebServiceGet
    -- Add the parameters for the stored procedure here
    @ServicePath varchar(500)='',
    @RequestBody varchar(1000)='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
   <soapenv:Header/>
   <soapenv:Body>
      <GetProgram xmlns="http://tempuri.org/">
      <Code>1513100000</Code>
    </GetProgram>
   </soapenv:Body>
</soapenv:Envelope>'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);
    Declare @RequestLength as int

    set @RequestLength=Len(@RequestBody)

    --Code Snippet
    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'POST','http://www.yourwebsite.com/service.asmx?op=GetProgram','false'
    EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'text/xml;charset=UTF-8'
    --EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'SOAPAction', 'POST'
    --EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Length', @RequestLength
    Exec sp_OAMethod @Object, 'send', NULL, @RequestBody
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT