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

问题描述

我正在尝试在TSQL中构建存储过程以调用Web服务。我以前在Oracle中完成过此操作，但在MSSQL中似乎并不那么容易。
当然，有很多原因不能在存储的proc中执行此操作，但是由于此过程仅用于日常批处理，因此性能并不是太大的问题。
我想做的事情如下：向Web服务发送一个全名，该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.

我在 http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from -TSQL-（存储过程）-使用-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 Server Management Studio。

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 /从SQL Server.htm中调用Web服务-但是，在安装Visual Studio 2012并创建 SQL Server数据库项目后，我无法在解决方案上下文菜单中选择添加Web引用，则菜单中只有nu这样的选项。

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

我用它来调用一个生成报告并通过该方法通过电子邮件发送报告的网络服务。

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

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