sp_invoke_external_rest_endpoint returns json parse error on @response

Question

i am running azure sql and call various MS graph and external rest endpoint via Azure API manager using the sp_invoke_external_rest_endpoint procedure.

On 1 instance calling the proc, I get the following error returned:
Msg 11558, Level 16, State 202, Procedure sp_invoke_external_rest_endpoint, Line 80 The @result JSON string could not be parsed. Please check the formatting of the JSON.

It would appear to me from the error that the json response passed from APIM to sp_invoke_external_rest_endpoint is incorrectly formed. The @result variable is not contained anywhere in my code and is defined and utilized in the sp_invoke_external_rest_endpoint proc.

I have read on other forums that a number of other users have encountered this issue but the solution is not clear.

I construct various variables to call the proc as below:

           exec @ret = sp_invoke_external_rest_endpoint 
                @url      = @urlString,
                @method   = @methodString, 
                @headers  = @headerString,
                @payload  = @body,
                @response = @response OUTPUT; 

The sp_invoke_external proc runs as expected but the outer proc fails due the above error.

I have tried running the sp_invoke_external proc without the @response line and it still fails with the above error since the error is occuring within the sp_invoke_external proc and not my code.

Please advise how to resolve.

Thanks

Shaun

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

Ensure that the API you are calling returns a well-formed JSON response. You can use tools like Postman or curl to call the endpoint directly and verify the response format.

Use a JSON validator (like jsonlint.com) to check the JSON structure. Any missing brackets, commas, or quotes can lead to parsing errors.

Ensure that the version of Azure SQL Database supports the features and the specific JSON format you're working with.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful