从TSQL存储过程调用SOAP Web服务

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.

我试图在TSQL中构建一个存储过程来调用Web服务。我以前在Oracle中做过这个,但在MSSQL中似乎并不那么容易。当然,有很多理由不在存储过程中执行此操作,但由于此过程仅用于每日批处理,因此性能不是太大的问题。我想要做的事情如下:向webservice发送一个全名,webservice将返回一个名称,分为名字,前缀,姓氏等。返回的值将需要写入表。

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.

我在http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)找到了一个有趣的程序 - 使用-MSXML.aspx似乎完全可以做到我想要什么,但只要你在调用中添加一个主体,我就会遇到“参数不正确”之类的错误。这篇文章也说明了这一点,显然没有简单的解决方案。我绝对需要发送请求正文。

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.

我还阅读了很多关于使用CLI或“Web服务任务编辑器”解决它的文章,或“SSIS”位,我找不到任何关于从哪里开始的教程。现在我只有Microsoft SQL服务器管理工​​作室。

I'm on SQL server 2012 by the way.

顺便说一下,我在SQL Server 2012上。

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.

我已经找到了这个描述,看起来很干净:http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm但是,在安装visual studio 2012并创建“SQL服务器数据库项目”后,我无法在解决方案上下文菜单中选择“添加Web引用”,菜单中只有这样的选项。

4 个解决方案

#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.

我用它来调用一个Web服务,它生成一个报告并在方法中通过电子邮件发送它。

#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

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.

在Visual Studio中维护SQL Server项目时,无法以常规方式添加Web引用。但是,您可以使用WSDL实用程序创建Web Interface并将其添加到您的解决方案中。随后,您将能够访问CLR存储过程中所需的Web方法。

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

WSDL.exe实用程序可以在已安装的Microsoft SDK中找到,我使用Windows 7版本运行,但是简单搜索你的hdd应该会给你你的位置,它安装在以下目录中:C:\ Program Files(x86) )\ Microsoft SDKs \ Windows \ v7.0A \ Bin

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

运行WSDL.exe实用程序时使用的命令是:

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.

这将在此实例中生成Weather.cs文件,然后您可以将其添加到项目中并在方法中调用。

#4

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

我也遇到了这个问题。以下是使用T-SQL中的参数执行HTTP Post的正确方法:

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: typo