Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
We created stored procedures in Azure SQL Database and trying to use them power BI report with Direct Query mode. But getting the error like "Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.
Another question is how to set the connection string as global so that I can use the same connection string in every report?
Please help me with the solution
I found a way to make it work with an On-Prem SQL Server 2016 database and Power BI Gateway: you need to copy the syntax that SQL Server generates when running a Stored Procedure.
In Power BI use the Advanced Editor to change your query to look something like this:
let
Source = Sql.Database("MySqlServerName", "MyDbName", [Query="DECLARE#(tab)@return_value int = 0; #(lf)#(lf)EXEC#(tab)@return_value = [MySchema].[MySP]; #(lf)#(lf)SELECT#(tab)'Return Value' = @return_value; "])
in
Source
Hope this helps in your Azure SQL DB,
I found a way to make it work with an On-Prem SQL Server 2016 database and Power BI Gateway: you need to copy the syntax that SQL Server generates when running a Stored Procedure.
In Power BI use the Advanced Editor to change your query to look something like this:
let
Source = Sql.Database("MyServerName", "MyDbName", [Query="DECLARE#(tab)@return_value int = 0; #(lf)#(lf)EXEC#(tab)@return_value = [MySchema].[MyStoredProcedure]; #(lf)#(lf)SELECT#(tab)'Return Value' = @return_value; "])
in
Source
Hope this helps in your Azure SQL DB.......................... -RM
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |