Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataEngineerAT
Frequent Visitor

REST API ExecuteQueries from Azure SQL (sys.sp_invoke_external_rest_endpoint)

The same query does work perfectly in Postman but not from Azure SQL (Management Studio):
"Msg 11558 The @payload JSON string could not be parsed"

 

All setup (credentials etc) is done, token is valid, other simple DAX queries do work from SQL but not all of them...!?
Tried different escape-char, also does not help.

 

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
}
}'

exec @ret = sys.sp_invoke_external_rest_endpoint
@method = 'POST',
@url = @url,
@payload = @payload,
@credential = [https://api.powerbi.com/v1.0/myorg/datasets/0de79exxxxxx2221de/executeQueries],
@response = @response output;

print @payload;

Result from Postman:

{
    "results": [
        {
            "tables": [
                {
                    "rows": [
                        {
                            "Projekte[Projekt]": "Admin",
                            "[Projekt_Stunden]": 515.0
                        },
                        {
                            "Projekte[Projekt]": "Projekt 2",
                            "[Projekt_Stunden]": 124.5
                        },
......

 

Result from Azure SQL:
Msg 11558 The @payload JSON string could not be parsed

1 ACCEPTED SOLUTION

I did find the reason!
Line breaks in the query string are not allowed, what makes it unuseable/unreadable with complex DAX

That 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
}
}';

 

View solution in original post

3 REPLIES 3
jaweher899
Super User
Super User

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 DAX

That 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
}
}';

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.