Sql Ole Automation Procedure sp_OAGetProperty 'responseText' not returning text results

Question

I am using SQL Ole Automation procedures to post content to Apis and then read the responses, all work successfully. I have run into an issue with a specific API, in the case when the Response Code is not 200 then the 'responseText' is not in a readable format. I ran the same request in Postman and I am getting regular json string in the response.

Sql Response:

sql response

Postman Response:

Postman response

I have tried to use sql sp_OAGetProperty @itoken, 'responseBody' to get the binary data but was unable to convert the response into readable text. sql binary result

This is the body of my procedure which posts to the API:

        exec @iOAProcReturnCode = sp_OACreate 'MSXML2.ServerXMLHTTP', @iToken OUT; 

        IF @iOAProcReturnCode <> 0 
        begin 
            select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to open HTTP connection.', @iOAProcReturnCode);
            throw 50000, @vchErrorMessage, 1
        end

        
        -- Set up the request.
        EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'open', NULL, 'POST', @vchUrl, 'false';
        if @vchAuthHeader > ''
        begin
            EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'setRequestHeader', Null, 'Authorization', @vchAuthHeader;
        end
        
        exec @iOAProcReturnCode = sp_OAMethod @iToken, 'setRequestHeader', null, 'Content-type', @vchContentType;

        -- Send the request
        EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'send', NULL, @vchBodyContent
        
        IF @iOAProcReturnCode <> 0 
        begin 
            select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to open connection and send request.', @iOAProcReturnCode);
            throw 50000, @vchErrorMessage, 1
        end

        --Read the response
        --This is what fails in the case of a non 200 statusCode
        insert @tResponseText
        (
            vchResponse
        )    
        EXEC sys.sp_OAGetProperty @iToken, 'responseText'
        IF @iOAProcReturnCode <> 0 
        begin 
            select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to get response text.', @iOAProcReturnCode);
            throw 50000, @vchErrorMessage, 1
        end

        exec @iOAProcReturnCode = sp_OAGetProperty @iToken, 'status', @vchStatusCode OUT;
        exec @iOAProcReturnCode = sp_OAGetProperty @iToken, 'statusText', @vchStatusText OUT;
        IF @iOAProcReturnCode <> 0 
        begin 
            select @vchErrorMessage = dbo.fnConcatOAErrorMessage('Unable to get status property.', @iOAProcReturnCode);
            throw 50000, @vchErrorMessage, 1
        end

Has anyone experienced this issue? Am I missing something?

Answer 1

Thanks @siggemannen for pointing me in the direction of comparing the headers. I was able to see that the Content-Encoding for a non 200 success code is gzip as @AlwaysLearning had already pointed out.

I was then able to cast(decompress(vchResponse) as varchar(max)) and get the response as a readable string.

This is the simplified version of my procedure which gets the response header and then decompresses it.

declare     
    @ContentEncoding varchar(400)
    declare @tResponseText table(vchResponse varbinary(max))

        exec @iOAProcReturnCode = sp_OACreate 'MSXML2.ServerXMLHTTP', @iToken OUT; 
        
        -- Send the request.    
        
        EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'send', NULL, @vchBodyContent    
        
        -- Read the response
        insert @tResponseText
        (
            vchResponse
        )    
        EXEC sys.sp_OAGetProperty @iToken, 'responseBody'


        EXEC @iOAProcReturnCode = sp_OAMethod @iToken, 'getResponseHeader', @ContentEncoding out, 'Content-Encoding'

         
        select @vchResponse =  case 
            when @ContentEncoding = 'gzip'
            then cast(decompress(vchResponse) as varchar(max))
            else cast(vchResponse as varchar(max))
        end
        from @tResponseText