Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |