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?
User contributions licensed under CC BY-SA 3.0