Forum Discussion
REST API ExecuteQueries from Azure SQL (sys.sp_invoke_external_rest_endpoint)
I did find the reason!
Line breaks in the query string are not allowed, what makes it unuseable/unreadable with complex DAXThat works, all in one line:
set @payload = N'{
"queries": [
{
"query": "DEFINE VAR __DS0Core = SUMMARIZECOLUMNS(''Projekte''[Projekt], \"Projekt_Stunden\", ''_Measures''[Projekt Stunden]) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, ''Projekte''[Projekt], 1) EVALUATE __DS0PrimaryWindowed ORDER BY ''Projekte''[Projekt]"
}
],
"serializerSettings": {
"includeNulls": true
}
}';
The error message you are receiving suggests that there is an issue with the JSON payload that you are trying to send to the REST API endpoint.
Based on the information you have provided, it seems that the payload is not being parsed correctly when using the sys.sp_invoke_external_rest_endpoint function from Azure SQL. However, the same payload works without issue when sent using Postman.
One thing to check is whether the @payload variable is being correctly formatted and passed as a valid JSON string. You may want to try using a JSON linter or validator to ensure that the payload is valid and does not contain any syntax errors.
Another thing to check is whether the encoding of the JSON string in the @payload variable is correct. Depending on the encoding used, it is possible that the JSON string is not being correctly interpreted by the REST API endpoint. You may want to check the encoding of the JSON string in both Postman and in your T-SQL code, and ensure that they are the same.
Additionally, you may want to check whether there are any differences in the headers or other settings being used in Postman compared to those in your T-SQL code. It is possible that there are additional headers or settings required to correctly format the JSON payload when using the sys.sp_invoke_external_rest_endpoint function.
If none of the above suggestions solve the issue, it may be helpful to provide more information on the specific error message being received, as well as any additional details on the API endpoint being used and the expected format of the JSON payload.
Well, the query string is 100% identical what postman sends and also the same as PowerBI desktop generates.
No glue what the SQL-Proc does expect...!? (There are also no headers sent to T-SQL)
{
"queries": [
{
"query": "
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS('Projekte'[Projekt], \"Projekt_Stunden\", '_Measures'[Projekt Stunden])
VAR __DS0PrimaryWindowed =
TOPN(501, __DS0Core, 'Projekte'[Projekt], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'Projekte'[Projekt]
"
}
],
"serializerSettings": {
"includeNulls": true
}
}
I did find the reason!
Line breaks in the query string are not allowed, what makes it unuseable/unreadable with complex DAXThat works, all in one line:
set @payload = N'{
"queries": [
{
"query": "DEFINE VAR __DS0Core = SUMMARIZECOLUMNS(''Projekte''[Projekt], \"Projekt_Stunden\", ''_Measures''[Projekt Stunden]) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, ''Projekte''[Projekt], 1) EVALUATE __DS0PrimaryWindowed ORDER BY ''Projekte''[Projekt]"
}
],
"serializerSettings": {
"includeNulls": true
}
}';