Forum Discussion
DirectQuery with stored procedure using temptables
Hello everybody,
According to this link I can circumvent the DirectQuery wrapping using OPENROWSET.
This would work, exept my stored procedure uses temptables.
This gives me the (SQL 2014) error:
The metadata could not be determined because statement 'Statement' in procedure 'ProcedureName' uses a temp table.
Another workaround seems to be using WITH RESULT SETS. This give me the same error as before with the DirectQuery and just the Stored Procedure:
Microsoft SQL: Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.
Are there more workaround into using DirectQuery's?
Hello everybody reading this,
I solved my problem by:
- Changing the Stored Procedure to a Function
- Changing the Temp Tables (#) to Table Variables (@)
7 Replies
Can you successfully execute the stored procedure using WITH RESULT SETS in SQL Server Management Studio(SSMS)? After you successfully select table using proper T-SQL in SSMS, embed the T-SQL in Advanced Editor of Power BI Desktop query.
= Sql.Database(“servername”, “dataabasename″, [Query=”SQL Statement”, CreateNavigationProperties=false])
If you still get errors, please post the full scripts in Advanced Editor of your query for us to analyze.
Regards,
LydiaHello Lydia
The Query runs perfectly in the Power Query Editor, when clicking Close & Apply it gives me the second error.
The Query I'm using still uses fixed values, I plan to connect Parameters to the Query.
Here is the Query:
let Source = Sql.Database(".", "DatabaseName", [Query="#(lf)#(lf)EXEC#(tab)[dbo].[GetDowntime] @Start = '2018-01-02 00:00:00', @Stop = '2018-01-30 00:00:00' WITH RESULT SETS ( (StopTime Datetime, Startagain Datetime,#(tab)[Timestamp] Datetime, Value INT, NextTS Datetime, ID INT, [Description] VARCHAR(150), color VARCHAR(150), LineName VARCHAR(150)#(tab)));#(lf)#(lf)", CreateNavigationProperties=false]) in SourceFunDeckHermit,
What type of parameter do you define in Power BI Desktop? And what is your code like in Advanced Editor after you adding parameter?
In addition, could you please post Create Command of your stored procedure in SQL Server? I will test it in my scenario.
Regards,
Lydia
