How to call webservice from TSQL? (SQL SERVER 2000)

Question

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;

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;

I get the following error:

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

When running

exec sp_configure

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

Update

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;

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

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

How I can call a webservice from TSQL?

Did you run sp_configure 'show advanced options', 1; on production as well? — Andomar
– Andomar, Commented Jan 10, 2013 at 14:07
Check the SQL Server versions on both your test and production servers. It sounds like they are different. — RBarryYoung
– RBarryYoung, Commented Jan 10, 2013 at 14:14
@Andomar, sp_configure 'show advanced options', 1; in the production server worked well. — Gonzalo
– Gonzalo, Commented Jan 10, 2013 at 14:14
What's the result of this query: SELECT * FROM master.dbo.sysobjects WHERE name LIKE '%sp_OA%' AND xtype = 'X' — Andomar
– Andomar, Commented Jan 10, 2013 at 14:29
@Andomar, the result is: sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAGetProperty, sp_OAMethod, sp_OASetProperty, sp_OAStop — Gonzalo
– Gonzalo, Commented Jan 10, 2013 at 14:57

indusBull · Accepted Answer · 2016-12-28 19:18:16Z

2

I just stumbled upon same error - "(0x8004271A ) Error in srv_convert."

To overcome char limitations, use #tmp table like below:

Create table #tmp(dt nvarchar(max))
insert into #tmp 
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText'
Select dt from #tmp -- single column/single row.
Drop Table #tmp -- clean up

Solution Source

answered Dec 28, 2016 at 19:18

indusBull

1,8545 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mingebag · Accepted Answer · 2013-01-10 14:12:13Z

1

heey i have maybe some help for you if you want to call to call a HTTP web service from T-SQL (no SQLCLR) You can automate the XMLHTTP server object using the Object Automation extended stored procedures.

Example

I suggest you use the CLR or an SSIS package though.

answered Jan 10, 2013 at 14:12

Mingebag

7582 gold badges20 silver badges35 bronze badges

1 Comment

Gonzalo Over a year ago

Thanks but in this case would also have to enable Ole Automation Procedure which is where I have the problem.

Gonzalo · Accepted Answer · 2013-01-11 12:21:18Z

1

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

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

answered Jan 11, 2013 at 12:21

Gonzalo

1411 gold badge3 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to call webservice from TSQL? (SQL SERVER 2000)

Update

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related