sp_OAMethod always returns NULL when consuming a Rest API... I'm have no idea where the problem could be. Here is the source code of the procedure:
CREATE PROCEDURE MakeCEPAbertoRequest
@cep VARCHAR(8) -- Tamanho do CEP no Brasil
AS
BEGIN
DECLARE @token VARCHAR(1000) = 'Token token=d142c65bc45454595b15897e1d70c04b6';
DECLARE @url VARCHAR(1000) = 'https://www.cepaberto.com/api/v3/cep?cep=' + @cep;
DECLARE @requestResult NVARCHAR(MAX);
DECLARE @Stat INT
DECLARE @resposta NVARCHAR(4000)
-- Criar um objeto XMLHTTP
DECLARE @objectID INT;
EXEC @Stat = sp_OACreate N'MSXML2.ServerXMLHTTP', @objectID OUT;
-- Abrir a conexão para o URL desejado
EXEC sp_OAMethod @objectID, N'open', NULL, N'GET', @url, false;
EXEC sp_OAMethod @objectID, N'setRequestHeader', NULL, 'Content-Type', 'application/json'
-- Definir o cabeçalho de autorização
EXEC sp_OAMethod @objectID, N'setRequestHeader', NULL, N'Authorization', @token;
-- Enviar a solicitação
EXEC sp_OAMethod @objectID, N'send';
-- Obter a resposta
EXEC sp_OAMethod @objectID, N'responseText', @requestResult OUTPUT;
-- Destruir o objeto XMLHTTP
EXEC sp_OADestroy @objectID;
-- Retorna a resposta
SELECT @requestResult AS Response
END
GO
I have created the equivalent of this procedure with CLR but the behavior is the same (I always get NULL output).
When I use this API in Insomnia (with the same parameters) I get the expected response.