I'm having an issue connecting to any API via SQL Server using the OA methods.
The code below is working on one of our servers (MS SQL Server 2019) but not another server (MS SQL Server 2012), both are on an internal ICN. The actual API is also on our ICN but I am having the same issue with the below tester.
On success I'm getting the XML, and by "not working" I just get a blank value with no error
I'm sure there is some setting I'm missing but not sure what to check. I have enabled the OLE Automation procs as per this question: SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'
In other questions people have suggested not using SQL, but for various corporate reasons I am constrained so please keep in mind
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
DECLARE @URL VARCHAR(255) = 'https://apps.learnwebservices.com/services/hello?WSDL'
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'get',
@URL,
'false'
EXEC sp_OAMethod @Object, 'send'
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
SELECT @ResponseText
EXEC sp_OADestroy @Object