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.
Hi there,
I have an Azure SQL Database that has a Stored Procedure that I need to call using Direct Query.
My Stored Procedure has no paramters.
I have scoured the forums and I have tried all 3 of the workarounds mentioned below using Advanced Options or Advanced Editor and I still get the following error:
Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near ')'.
The workarounds I have tried are as follows:
Workaround # 1
DECLARE @SP VARCHAR(100) = 'SCHEMA.STOREDPROCEDURE'
EXEC (@SP)
Workaround # 2
let
Source = Sql.Database("SQL-AZURE-DATABASE", "SQL-AZURE-DATABASENAME", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 'exec SCHEMA.STOREDPROCEDURE')", CreateNavigationProperties=false])
in
Source
Workaround # 3
let
Source = Sql.Database("SQL-AZURE-DATABASE", "SQL-AZURE-DATABASENAME", [Query="DECLARE#(tab)@return_value int = 0; #(lf)#(lf)EXEC#(tab)@return_value = [SCHEMA].[STOREDPROCEDURE]; #(lf)#(lf)SELECT#(tab)'Return Value' = @return_value; "])
in
Source
Has anyone managed to do this in the above scenario?
Many thanks.
Solved! Go to Solution.
Try to specify database name inside OPENROWSET.
The problem is that behind the scenes Power BI executes
select * from ( --your query, e.g. EXEC spReport ) SourceQuery where 1 = 2
To test source validity. This is incorrect syntax when query is EXEC
Try to specify database name inside OPENROWSET.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |