DirectQuery with stored procedure using temptables

[FunDeckHermit]

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?

[v-yuezhe-msft]

@FunDeckHermit,

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

[FunDeckHermit]

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

[v-yuezhe-msft]

@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

[FunDeckHermit]

@v-yuezhe-msft

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

[FunDeckHermit · Accepted solution]

Hello everybody reading this,

I solved my problem by:

1. Changing the Stored Procedure to a Function
2. Changing the Temp Tables (#) to Table Variables (@)

[adamtappis]

@FunDeckHermit

It appears this is still an issue in the latest (September 2018) build of PowerBI Desktop. Unfortunately your workaround will not work for me.

@v-yuezhe-msft

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?

[adamtappis]

I resolved my issue by adding the WITH RESULT SETS clause to the stored precedure call, nested inside of the OPENQUERY function