How To Call Webservice From Tsql? (sql Server 2000)

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

Solution 1:

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

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

Createtable #tmp(dt nvarchar(max))
insertinto #tmp 
exec@hr=sp_OAGetProperty @objWinHttp, 'ResponseText'Select dt from #tmp -- single column/single row.DropTable #tmp -- clean up

Solution Source

Solution 2:

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.

Solution 3:

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

if WScript.Arguments.Count = 1thenSet 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."endif

Then in TSQL:

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