I have the following code sample that calls a Web API method:
DECLARE @Object AS int;
DECLARE @ResponSEText AS Varchar(8000);
DECLARE @Token AS Varchar(8000);
DECLARE @xmltest AS Varchar(8000);
DECLARE @hResult AS int
DECLARE @source varchar(255), @desc varchar(255)
DECLARE @LocationId varchar(25);
DECLARE @Body AS varchar(8000) = '{
"UserName": "username",
"Password": "password"
}'
--EXEC sp_OACREATE 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OACREATE 'MSXML2.ServerXMLHttp', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'POST', 'https://urgentcargus.azure-api.net/api/LoginUser', 'false'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @len int
SET @len = len(@body)
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Ocp-Apim-Subscription-Key','23cb2410112b4c01bc397bcda0d18487'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Ocp-Apim-Trace:true'
EXEC sp_OAMethod @Object, 'SETRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'SETRequestBody', null, 'Body', @body
EXEC sp_OAMethod @Object, 'Send', null, @body
EXEC sp_OAMethod @Object, 'responSEText', @ResponSEText OUTPUT
SELECT @Token=@ResponSEText
EXEC sp_OADestroy @Object
SET @Token ='Bearer '+ REPLACE(@Token,'"','')
PRINT @Token
On my laptop (Win10, SQL Server 2017), I have a response of less than a second. However, the same code sample used on a customer's server (Windows Server 2012R2, SQL Server 2017) takes almost 21 seconds to get a response and even when it returns NULL.
Is there something that I'm missing? I'm guessing it has to do something related to the server settings and how it goes out, on the web. Thanks
Is the server going out through a proxy or directly? If it's directly without using any proxy, maybe adding one might help. I had the same issue with Database Mail from SQL and until setting a proxy and adding the port SQL is using, I wasn't able to send any e-mails.