SSRS Temp Table from previous dataset stopped working

Question

I am creating an SSRS with multiple pages for related data.

A couple of queries from the initial dataset are putting some data in a couple of temp tables that I use in subsequent datasets.

The report was running correctly until a while ago when I changed one of the subsequent queries to use a report parameter for a date instead of creating it in the query.

Now I am suddenly getting errors about the Temp Tables not existing.

Query execution failed for dataset 'MEMBER_DATA'.

Invalid object name '#VENDORS'.

I tried Undoing the changes but it's still giving me the error (WTF?!?!). I get the error in Report Manager and Visual Studio.

The Use Single Transaction box is still checked for the only data source. I checked with the DBAs and they haven't done anything with our TEST server today.

How could I make the temp tables with multiple datasets work again?

Answer 1

How are you creating the temporary table? CREATE TABLE #VENDORS ...?

Local temporary tables are deleted when the connection that created them is closed. They are visible only to their creators on the same connection used to create the tables. Generally that means that creating a temporary table inside a stored procedure and trying to access it outside the stored procedure won't work. Local temporary tables are deleted after the user disconnects. Even though you have checked "Use single transaction" for the DataSource it sounds like the connection is being closed before the Datasets can read the temporary table or the stored procedure is executing under a different connection or user.

You could try creating it as a global temporary table: CREATE TABLE ##VENDORS .... Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect. That might keep the table alive long enough for the readers to read them.

Note that this makes the temporary table accessible to all users which may be undesirable.

Answer 2

After (much) further review, it seems that the #TEMP Table use in SSRS falls apart when you use an SSRS parameter in your query.

I have figured out that I can use the query expression to add the parameter to the query text.

="SELECT M_ID," & VBCRLF & 
"      CONVERT(VARCHAR(10), APPT_TIME, 120) AS DOS," & VBCRLF &  
"      STATUS," & VBCRLF & 
"      COUNT(*) AS TRANSACTIONS," & VBCRLF & 
"      CAST(SUM(BILL_FROM_TRIP_COST) AS MONEY) AS ADMIN_FEE," & VBCRLF & 
"      CAST(SUM(LYFT_TRIP_COST) AS MONEY) AS TRANSPORATION_COST," & VBCRLF & 
"      CAST(0.00 AS MONEY) AS TOTAL_COST" & VBCRLF & 
"INTO #ALC " & VBCRLF & 
"FROM DETAILS WITH(NOLOCK)" & VBCRLF & 
"WHERE CONVERT(VARCHAR(6), APPT_TIME, 112) <= '" & Parameters!CUTOFF_MONTH.Value & "'" & VBCRLF & 
"GROUP BY M_ID," & VBCRLF & 
"        CONVERT(VARCHAR(10), APPT_TIME, 120)," & VBCRLF & 
"        STATUS;" 

I cannot find any documentation of such a limitation anywhere. If anyone knows of anything that has the issue, feel free to add or post your own answer.

Answer 3

I was having the same problem. I believe SSRS encapsulates the dataset query in a temporary stored procedure when you use Parameters. Any temporary table declared within a stored procedure goes out of scope once the stored procedure ends.

To get around this, I declared my temporary tables in a separate dataset that runs first. This requires you to modify the XML of the .RDL file if you've already created datasets, but shouldn't be a problem for new reports.

One other way, is to create a temp table in the first dataset where each field refers to your parameter. In a second dataset, you populate the temp table from the parameters. Then in your real datasets, you query the temp table to retrieve your parameters without having any issues.