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.
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?
Solved! Go to Solution.
Hello everybody reading this,
I solved my problem by:
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,
Lydia
Hello 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
Source
@FunDeckHermit,
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
Hello Lydia,
For debugging purposes i'm not using the parameters. They do not influence the query what so ever.
They are not used and will only be used if the I can get the Stored Procedure working with fixed hardcoded parameters.
The stored procedure is quite complex so I condensed it to a minimum and called it GetDowntimePeriods3.
CREATE PROCEDURE [dbo].[GetDowntimePeriods3] @Start DATETIME = '2018-02-01', @Stop DATETIME = '2018-03-01', @MachineLineNr INT = 0 AS SET NOCOUNT ON IF OBJECT_ID('tempdb..#ExtendedDowntimeLOG') IS NOT NULL DROP TABLE #ExtendedDowntimeLOG IF OBJECT_ID('tempdb..#DowntimeLOG') IS NOT NULL DROP TABLE #DowntimeLOG SELECT * INTO #DowntimeLOG FROM BIT_Log WHERE [TagID] = 445
SELECT *, LEAD(Timestamp,1) OVER (ORDER BY Timestamp) as NextTS INTO #ExtendedDowntimeLOG FROM #DowntimeLOG UPDATE #ExtendedDowntimeLOG SET Value = -1 WHERE [Timestamp] = NextTS Select *, 'Koekjeslijn 1' as LineName From #ExtendedDowntimeLOG drop table #DowntimeLOG drop table #ExtendedDowntimeLOG GO
The stored procedure has default parameters, primairily used for debugging purposes. This condensed version only uses data from one source: BIT_Log. A .csv file of the table can be fount here: LINK.
The text inside the Advanced Query Editor is the following:
let Source = Sql.Database(".", "MyDatabaseName", [Query="#(lf)#(lf)EXEC#(tab)[dbo].[GetDowntimePeriods3] WITH RESULT SETS ( ([Timestamp] Datetime, TagID INT, Value INT, NextTS Datetime, LineName VARCHAR(150)#(tab)));#(lf)#(lf)", HierarchicalNavigation=true]) in Source
Hello everybody reading this,
I solved my problem by:
It appears this is still an issue in the latest (September 2018) build of PowerBI Desktop. Unfortunately your workaround will not work for me.
Is there any update from the PowerBI development team on this? Why would the query run just fine in the DirectQuery Editior but then fail with a syntax error when the "Apply Changes" button is pressed?
I resolved my issue by adding the WITH RESULT SETS clause to the stored precedure call, nested inside of the OPENQUERY function
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.