从 TSQL 存储过程调用 SOAP 网络服务 [英] Calling a SOAP webservice from TSQL stored procedure

问题描述

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

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.

我在 http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx 这似乎完全符合我的要求，但是一旦您向调用添加主体，我就会遇到诸如参数不正确"之类的错误.这在文章中也有说明，显然没有简单的解决方案.我肯定需要发送一个请求正文.

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.

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

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.

顺便说一下，我使用的是 SQL Server 2012.

I'm on SQL server 2012 by the way.

关于我应该朝哪个方向发展的任何想法?

Any ideas about what direction I should go with this?

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

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.

推荐答案

过去我使用过以下方法，这可能不是现在最好的方法，但对我来说很成功:

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

我用它来调用一个 web 服务，该服务生成一个报告并在该方法中通过电子邮件发送.

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

这篇关于从 TSQL 存储过程调用 SOAP 网络服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！