如何从 TSQL 调用 webservice?(SQL SERVER 2000)

问题描述：

我想从 SQL Server 2000 中的 TSQL 调用 Web 服务.我尝试使用以下代码:

I want to call a webservice from TSQL in SQL Server 2000. I tried with the following code:

Declare @Object as Int; 
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get','http://server/ws/service1.asmx/Test', 'false';
Exec sp_OAMethod @Object, 'send';
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;
Select @ResponseText Resultado;
Exec sp_OADestroy @Object;

为此，我必须启用 Ole 自动化:

For this to work I had to enable Ole Automation:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

在我的测试服务器上工作正常，问题是在生产服务器上运行

In my test server works fine, the problem is that on the production server to run

sp_configure 'Ole Automation Procedures', 1;

我收到以下错误:

配置选项Ole 自动化程序"不存在，或者它可能是一个高级选项.

The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.

运行时

exec sp_configure

在测试服务器上没有在生产服务器上带来记录Ole Automation Procedures".

on the test server brings the record "Ole Automation Procedures" on the production server not.

我修改代码以捕获错误:

I modify the code to catch the error:

Declare @Object as Int; 
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get','http://server/ws/service1.asmx/Test', 'false';
Exec sp_OAMethod @Object, 'send';
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT;
EXEC sp_OAGetErrorInfo @Object
Select @ResponseText Resultado;
Exec sp_OADestroy @Object;

指令sp_OAGetErrorInfo EXEC@Object"返回:(0x8004271A) srv_convert 中的错误.

The instruction "sp_OAGetErrorInfo EXEC @ Object" return: (0x8004271A ) Error in srv_convert.

根据 Microsoft(link)是一个SqlServer 的问题.因为就我而言，网络服务的结果超过 4000 个字符.

According to Microsoft (link) is a problem of SqlServer. Since in my case the result of the webservice exceed 4000 characters.

如何从 TSQL 调用网络服务?

答

我是通过以下方式解决的:
使用以下代码创建 VBScript 文件 (callWS.vbs):

I solved it the following way:
Create a VBScript file (callWS.vbs) with the following code:

if WScript.Arguments.Count = 1 then
    Set http = CreateObject("Microsoft.XmlHttp")
    http.open "GET", WScript.Arguments(0), FALSE
    http.send ""
    WScript.Echo http.responseText
else
    WScript.Echo "Not was provided the WS address."
end if

然后在 TSQL 中:

Then in TSQL:

declare @Command varchar(100)
declare @RetInfo varchar(8000)
select @Command = 'cscript c:\callWS.vbs "http://server/ws/service1.asmx/Test"'
print @Command
exec @RetInfo = master.dbo.xp_cmdshell @Command
print @RetInfo

如何从 TSQL 调用 webservice?(SQL SERVER 2000)

相关推荐