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