Calling a SOAP webservice from TSQL stored procedu

问题:

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.

回答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.

回答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

回答3:

I ran into this issue as well. Here is the proper way to execute an HTTP Post with parameters from T-SQL:

DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);

SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
SET @contentType = 'application/x-www-form-urlencoded';
SET @postData = 'value1=Hello&value2=World'
SET @url = 'https://requestb.in/16xdq1p1'

-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

Credit goes to the original author.

Edit: The example service I was calling here appears to have shut down. You will need to swap out the URL and post to a different endpoint in order to see it working.

回答4:

You cannot add a Web Reference in the usual way when maintaining a SQL Server Project in Visual Studio. However, you can use the WSDL Utility to create the Web Interface and add this to your solution. Subsequently, you will then be able to access the Web Methods that you want in your CLR Stored Procedure.

The WSDL.exe utility can be found within the installed Microsoft SDK's, I ran mine using the Windows 7 version but a simple search of your hdd should give you your location, which was installed in the following directory : C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin

The commands to use when running the WSDL.exe utility are :

WSDL.exe /o:(name of Visual Studio Class file) /n:(name of namespace) (address of webservice)

for example:

WSDL.exe /o:Weather.cs /n:Weather.Test http://http://wsf.cdyne.com/WeatherWS/Weather.asmx

This will generate a Weather.cs file in this instance which you can then add to your project and call within your method.