Microsoft SQL: Incorrect syntax near the keyword 'exec'. Incorrect syntax near ')'.

[Anonymous]

I have a SP in Azure SQL Database, the SP runs fine in azure and into the transform (power query) window, but it's unable to load into the data model. It returns back Microsoft SQL: Incorrect syntax near the keyword 'exec'.
Incorrect syntax near ')'.

[eduardosa]

I'm getting this error on calling MSSQL Stored Procedures by Power Query, when:

1. Calling more than one transformation on parameters,
2. Calling Stored Procedure in Direct Query mode.

Both the scenarios I'm showing work on the Power Query Editor.

The error shows when loading the data onto the PowerBI file.

This seems to be a bug on the interface that calls the MSSQL code. From my point of view it should not require the work around of calling the SP by a function, as it was suggested.

I've been able to call the SP without the need for the "fuction", (this code works on Import Mode), and load data into PowerBI.

Sql.Database(#"Server", #"DB", [Query="EXEC [Stored_Procedure] '" & Date.ToText( RangeStart_Date , [Format="yyyy-MM-dd"] ) & "', '" & Date.ToText( RangeEnd_Date, [Format="yyyy-MM-dd"] ) & "'"])

However when I call an extra date transformation, from DateTime -> Date, the code throughs the error: Microsoft SQL: Incorrect syntax near the keyword 'exec'. Incorrect syntax near ')'

Sql.Database(#"Server", #"DB", [Query="EXEC [Stored_Procedure] '" & Date.ToText( DateTime.Date( RangeStart ) , [Format="yyyy-MM-dd"] ) & "', '" & Date.ToText( DateTime.Date( RangeEnd_Date ), [Format="yyyy-MM-dd"] ) & "'"])

What's happening here?

[yatheeshaworks]

same issue but i need in direct query is it any solution.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Do you import data or use direct-query?

Check the document below to see if you do the right thing.

https://www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-pow...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

my mistake I must have been using direct query on accident. Works fine today on import