calling a web service from SQL with sp_OAMethod

0

I work on SQL Server 2016. I am trying to call a web service from a SQL stored procedure. I always got an error 0x80072EE2 (send failed) when calling the send method

I already looked up several articles here an so far all solutions did not work for me. The funny thing is that other web service calls from other stored procedures work well, so I suspect that it has sth to do with getting the destination URL known to the server. I already looked up in the host file but there is nothing in it but the standard entries

What I tried was this:

DECLARE @methodName varchar(50) = 'GET'
DECLARE @soapAction varchar(255) = '?serviceEndpoint=%2Fsurveys%2F767940&apiKey=96141f09-7396-e33e-f6fb-bcc8f5aaeb28&method=get'
DECLARE @requestBody varchar(8000) = '' 

declare @statusText varchar(1000)
declare @status varchar(1000) 
DECLARE @responseText nvarchar(max) = ''
DECLARE @errorText nvarchar(max) =''

DECLARE @UriIntern varchar(1000) = 'http://stage.pantaenius.com/pantaenius-website/application/netigate/request.do'
declare @completeUrl nvarchar(1000) = @UriIntern +@soapAction

DECLARE @Object INT;


DECLARE @methodName varchar(50) = 'GET'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255) 

DECLARE @contentType nvarchar(100) = 'application/json; charset=utf-8'
--'MSXML2.XMLHTTP'
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT

--EXEC @rc   =   sp_OASetProperty   @objServHTTP,'setTimeouts',resolveTimeout,connectTimeout,sendTimeout,receiveTimeout
EXEC @hResult   =   sp_OASetProperty   @objectID,'setTimeouts','5000','5000','50000','30000'
-- open the destination URI with Specified method 
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName,@completeUrl, 'false', '',''

-- send the request 
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 
      return
END


-- Get status text 
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out

select @status, @statusText, @methodName 

-- Get response text 
exec sp_OAGetProperty @objectID, 'responseText', @responseText out

destroy: 
exec sp_OADestroy @objectID

select @responseText, @status, @errorText

I know the example is not executable because I do not provide the correct URL because for security reasons. But the URL and its query parameters are correct - I get a correct response when I enter them in i.e. chrome or firefox It is just the SQL call which does not work.

Could anyone help?

sql
web-services
asked on Stack Overflow Feb 3, 2020 by Carsten • edited Feb 3, 2020 by Carsten

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0