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?

*DON'T* use OLE automation to begin with. That's *not* the way to make HTTP calls from inside SQL Server, assuming there's any valid reason to do so. `MSXML2.ServerXMLHTTP` is a 20+-year old library used by VBScript that depends on Internet Explorer. This won't even run on Linux and requires seriously weakening security on Windows — Panagiotis Kanavos, Jun 20 '23 at 15:41
What are you trying to do? Why not make the call in a *client* application or script? In supported SQL Server versions you can also use Python scripts to make calls using libraries that actually support token authentication, something that didn't exist in the 1990s when `MSXML2.ServerXMLHTTP` was introduced. — Panagiotis Kanavos, Jun 20 '23 at 15:44
For example, the error tells you that the token you used has expired and needs refreshing. That requires a loop at least. That's easy to do in C# or Python where the HTTP call is a single line. All the code in this question is trying to make a *single* call and never even tries to parse the response. How will you write the code to re-authenticate in T-SQL ? — Panagiotis Kanavos, Jun 20 '23 at 15:46
Are you trying to call a FedEx service? Googling for the error message returns the [FedEx API docs](https://developer.fedex.com/api/en-my/guides/best-practices.html) as the first result. As the docs show, FedEx, like almost all APIs, uses OAuth. That's an entire process to login with user or service credentials, retrieve a token and refresh it when needed. That involves a *lot* of calls and redirections — Panagiotis Kanavos, Jun 20 '23 at 15:50
i'm guessing the api doesn't set headers correctly for errors, you could compare the headers between successful and not successful code and see the difference. But anyway, if the call fails, do you need to know the response text? And yes, as above speaker wrote, OA_-methods kinda suck :) — siggemannen, Jun 20 '23 at 16:06
If you looked at the `Content-Encoding` response header the reason would become clear: it's a `gzip` compressed response. Good luck decompressing that in T-SQL script. SQL Server is not the place to be managing HTTP requests and responses like this, do yourself a favour and use an external process to make HTTP requests, manage the responses and write them back to the database. — AlwaysLearning, Jun 20 '23 at 21:57
Thank you @siggemannen and @AlwaysLearning for pointing me in the direction of checking the response header `Content-Encoding`. I was able to `cast(decompress(vchResponse) as varchar(max))` and get the response as readable text. I will respond with the answer. Regarding not using OLE automation, we needed a way of making synchronous api calls from the sql database and this is working well for our use case. — ym185, Jun 21 '23 at 17:18

score 0 · Accepted Answer · answered Jun 21 '23 at 17:19

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

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

1 Answers1