Calling a web service from T-SQL (Stored Procedure) using MSXML

Question

EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 
    IF    @hResult <> 0 
    BEGIN
          EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
          SELECT      hResult = convert(varbinary(4), @hResult), 
                source = @source, 
                description = @desc, 
                FailPoint = 'Send failed', 
                MedthodName = @methodName 
          goto destroy

When I Call a web service Through Sp IN SQl Server In Above Mention I'm getting error as Shown In The Pics

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

Not sure about that exact error, but I would advise against using the OLE Automation stored procedures (i.e. sp_OA*) as they have been deprecated since SQL Server 2005 was released and have various "issues".

I you need to interact with a Web Service, your best bet is to use SQLCLR. I have more info regarding this option in the following answer, also here on DBA.SE:

Bringing web service data into SQL server

Calling a web service from T-SQL (Stored Procedure) using MSXML

1 Answers1