sp_invoke_external_rest_endpoint JSON字符串无法解析

huangapple go评论63阅读模式
英文:

sp_invoke_external_rest_endpoint JSON string could not be parsed

问题

以下是您要翻译的内容:

"Trying out HTTP GET from Azure SQL as per @mauridb's article https://devblogs.microsoft.com/azure-sql/azure-sql-database-external-rest-endpoints-integration-public-preview/

Response: is 200, {"Success":true}
but Azure Data Studio shows:

    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.

Sample code:

    DECLARE
        @url nvarchar(4000) = 'https://ifconfig.me/all.json'--put Azure Function Http GET endpoint
        ,@headers nvarchar(max) = N'{"x-functions-key":"keygoeshere"}'
        ,@payload nvarchar(max) = NULL
        ,@ret int
        ,@response nvarchar(max)
        ;

    exec @ret = sp_invoke_external_rest_endpoint 
        @url = @url,
        @method = 'GET',
        @headers = @headers,
        @payload = @payload,
        @response = @response output;
        
    select @ret as ReturnCode, @response as Response;
英文:

Trying out HTTP GET from Azure SQL as per @mauridb's article https://devblogs.microsoft.com/azure-sql/azure-sql-database-external-rest-endpoints-integration-public-preview/

Response: is 200, {"Success":true}
but Azure Data Studio shows:

    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.

Sample code:

    DECLARE
        @url nvarchar(4000) = 'https://ifconfig.me/all.json'--put Azure Function Http GET endpoint
        ,@headers nvarchar(max) = N'{"x-functions-key":"keygoeshere"}'
        ,@payload nvarchar(max) = NULL
        ,@ret int
        ,@response nvarchar(max)
        ;

    exec @ret = sp_invoke_external_rest_endpoint 
        @url = @url,
        @method = 'GET',
        @headers = @headers,
        @payload = @payload,
        @response = @response output;
        
    select @ret as ReturnCode, @response as Response;

答案1

得分: 1

在 @payload 部分,您需要指定查询。请在一行中指定查询,因为不支持换行。

set @payload = N'{
    "queries": [
    {
    "query": "您的查询"
    }
    ],
    "serializerSettings": {
    "includeNulls": true
    }
    }';

为了获得最佳用户体验,请下载并安装最新的Azure Data Studio或SQL Server Management Studio。

英文:

On the @payload you need to specify the query. Specify the query in one line, because line breaks are not supported.

set @payload = N'{
"queries": [
{
"query": "Your query"
}
],
"serializerSettings": {
"includeNulls": true
}
}';

Download and install the latest Azure Data Studio or SQL Server Management Studio for the best user experience.

答案2

得分: 1

在回应给 @Alberto Morillo 时,您可以尝试以下解决方案。我在我的环境中也遇到了相同的错误。

sp_invoke_external_rest_endpoint JSON字符串无法解析

这里的问题出在解析 JSON 对象上。与您请求的函数应用中的响应有关。

sp_invoke_external_rest_endpoint JSON字符串无法解析

上面是我的函数响应。在这里,您可以看到返回类型是字符串。这就是您遇到此错误的原因。根据此文档结果应该是 JSON 格式。

因此,请将您的 Azure 函数应用响应修改如下。基本上是 JSON 格式。

sp_invoke_external_rest_endpoint JSON字符串无法解析

现在在数据工作室中执行,应该能够获得成功的结果。

sp_invoke_external_rest_endpoint JSON字符串无法解析

响应:
sp_invoke_external_rest_endpoint JSON字符串无法解析

此外,您可以按照以下方式查询响应。

SELECT * FROM OPENJSON(@response,'$.result')

sp_invoke_external_rest_endpoint JSON字符串无法解析

英文:

On addition to @Alberto Morillo.

You try this below solution.

Even I got same error in my environment.

sp_invoke_external_rest_endpoint JSON字符串无法解析

Here, the error is with parsing the json object.
That is something related to response in the function app where you are requesting.

sp_invoke_external_rest_endpoint JSON字符串无法解析

Above are my function responses. Here you can see the return type is string. So that is the reason you are getting this error.
As per this document the result should be in json format.

So modify your azure function app response as below.
Basically, as Json format.

sp_invoke_external_rest_endpoint JSON字符串无法解析

Now executed in data studio got successful result.

sp_invoke_external_rest_endpoint JSON字符串无法解析

Response:
sp_invoke_external_rest_endpoint JSON字符串无法解析

Again, you can query the response as below.

SELECT  *  from  openjson(@response,'$.result')

sp_invoke_external_rest_endpoint JSON字符串无法解析

huangapple
  • 本文由 发表于 2023年7月6日 17:09:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/76627242.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定