英文:
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 时,您可以尝试以下解决方案。我在我的环境中也遇到了相同的错误。
这里的问题出在解析 JSON 对象上。与您请求的函数应用中的响应有关。
上面是我的函数响应。在这里,您可以看到返回类型是字符串。这就是您遇到此错误的原因。根据此文档,结果应该是 JSON 格式。
因此,请将您的 Azure 函数应用响应修改如下。基本上是 JSON 格式。
现在在数据工作室中执行,应该能够获得成功的结果。
响应:
此外,您可以按照以下方式查询响应。
SELECT * FROM OPENJSON(@response,'$.result')
英文:
On addition to @Alberto Morillo.
You try this below solution.
Even I got same error in my environment.
Here, the error is with parsing the json object.
That is something related to response in the function app where you are requesting.
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.
Now executed in data studio got successful result.
Response:
Again, you can query the response as below.
SELECT * from openjson(@response,'$.result')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论