Calling a SOAP webservice from TSQL stored procedure

Question

I am trying to build a stored procedure in TSQL to call a webservice. I've done this before in Oracle, but it seems like it's not so easy in MSSQL. There are of course many reasons not to do this in a stored proc, but since this procedure is only to be used in a daily batch, performance is not too much of a issue. The thing I want to do is as follows: Send a full name to the webservice, the webservice will return a name divided in things like first name, prefix, lastname, etc. The returned values will need to be written to a table.

I found a interesting procedure at http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx wich seemed to do exactly what I want, but as soon as you add a body to the call, I run into errors like "The parameter is incorrect". This is also stated in the article, and apparently there's no easy solution for it. I definitely need to send a request body.

I also read lots of articles about solving it with CLI or the "Web Service Task Editor", or "SSIS" bit I couldn't find any tutorials about where to start. Right now I only have Microsoft SQL server management studio.

I'm on SQL server 2012 by the way.

Any ideas about what direction I should go with this?

I've already found this description, wich seems pretty clean: http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm However, after installing visual studio 2012 and creating a "SQL server database project", I am unable to choose "Add Web Reference" in the solution context menu, there's just nu such option in the menu.

Answer 1

In the past I have used the following method, it may not be the best method these days but it has worked successfully for me :

DECLARE @obj int,
        @url VarChar(MAX),
        @response VarChar(MAX),
        @requestHeader VarChar(MAX),
        @requestBody VarChar(MAX)

SET @url = 'http://....'

SET @requestBody = '<soapenv:Envelope>
                     <soapenv:Header/>
                      <soapenv:Body>
                       ...
                      </soapenv:Body>
                     </soapenv:Envelope>'

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, false
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'text/xml;charset=UTF-8'
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'SOAPAction', 'POST'
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Length', LEN(@requestBody)
EXEC sp_OAMethod @obj, 'send', NULL, @requestBody
EXEC sp_OAGetProperty @obj, 'responseText', @response OUT


SELECT @response [RESPONSE]

EXEC sp_OADestroy @obj

I have used this to call a webservice which produces a report and emails it within the method.

Answer 2

Made this monster for my own needs

CREATE PROCEDURE [dbo].[RequestHttpWebService]
@Url varchar(1024),
@HttpMethod varchar(10),
@ParamsValues varchar(1024),    -- param1=value&param2=value
@SoapAction varchar(1024) = null
AS
BEGIN
SET NOCOUNT ON;

--set @Url = 'http://localhost/service.asmx'
--set @HttpMethod = 'soap'
--set @ParamsValues = 'login=tr2280&password=Qwe12345&domain=webtech.development'
--set @SoapAction = 'Authenticate'


if @HttpMethod in ('get','GET') and len(@ParamsValues) > 0
begin
    set @Url = @Url + '?' + @ParamsValues
end

declare @obj int
    ,@response varchar(8000)
    ,@responseXml xml
    ,@status varchar(50)
    ,@statusText varchar(1024)
    ,@method varchar(10) = (case when @HttpMethod in ('soap','SOAP') then 'POST' else @HttpMethod end)

exec sp_OACreate 'MSXML2.ServerXMLHttp', @obj out
exec sp_OAMethod @obj, 'Open', null, @method, @Url, false

if @HttpMethod in ('get','GET')
begin
    exec sp_OAMethod @obj, 'send'
end
else if @HttpMethod in ('post','POST')
begin
    exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
    exec sp_OAMethod @obj, 'send', null, @ParamsValues
end
else if @HttpMethod in ('soap','SOAP')
begin
    if @SoapAction is null
        raiserror('@SoapAction is null', 10, 1)

    declare @host varchar(1024) = @Url
    if @host like 'http://%'
        set @host = right(@host, len(@host) - 7)
    else if @host like 'https://%'
        set @host = right(@host, len(@host) - 8)

    if charindex(':', @host) > 0 and charindex(':', @host) < charindex('/', @host)
        set @host = left(@host, charindex(':', @host) - 1)
    else 
        set @host = left(@host, charindex('/', @host) - 1)

    declare @envelope varchar(8000) = '<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><{action} xmlns="http://tempuri.org/">{params}</{action}></soap:Body></soap:Envelope>'
    declare @params varchar(8000) = '' 

    WHILE LEN(@ParamsValues) > 0
    BEGIN
        declare @param varchar(256),
                @value varchar(256)

        IF charindex('&', @ParamsValues) > 0
        BEGIN

            SET @param = left(@ParamsValues, charindex('&', @ParamsValues) - 1)
            set @value = RIGHT(@param, len(@param) - charindex('=', @param))
            set @param = left(@param, charindex('=', @param) - 1)
            set @params = @params + '<' + @param + '>' + @value + '</'+ @param + '>'
            SET @ParamsValues = right(@ParamsValues, LEN(@ParamsValues) - LEN(@param + '=' + @value + '&'))
        END
        ELSE
        BEGIN
            set @value = RIGHT(@ParamsValues, len(@ParamsValues) - charindex('=', @ParamsValues))
            set @param = left(@ParamsValues, charindex('=', @ParamsValues) - 1)

            set @params = @params + '<' + @param + '>' + @value + '</'+ @param + '>'
            SET @ParamsValues = NULL
        END
    END

    set @envelope = replace(@envelope, '{action}', @SoapAction)
    set @envelope = replace(@envelope, '{params}', @params)

    set @SoapAction = 'http://tempuri.org/' + @SoapAction

    print @host
    print @SoapAction
    print @envelope

    exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'
    exec sp_OAMethod @obj, 'setRequestHeader', null, 'Host', @host
    exec sp_OAMethod @obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction
    exec sp_OAMethod @obj, 'send', null, @envelope
end

exec sp_OAGetProperty @obj, 'responseText', @response out
exec sp_OADestroy @obj

select @status as [status], @statusText as [statusText], @response as [response]
END
GO

edit: formatting