Issue with SSRS report with temp table

Question

To be very clear at the moment. I would like to not use a stored procedure to create my temp table!

I am new to SSRS reports and currently trying to complete a large report. I have multiple datasets all using pretty much the same information in each. I tried creating a temp table in the first dataset and in the last dataset dropping the table however the report is not running. So I have some questions.

Say for example I have the following

-----------------                           --------------
|Data-set 1     |---------------------------|Data Source |
|===============|               |           |============|
|Input          |               |           |Use Single  |
|===============|               |           |Transaction |
|@MyDate        |               |           |enable      |
|@ProcessorId   |               |           |============|
|===============|               |
|Variables      |               |
|===============|               |
|@StartDate     |               |
|@EndDate       |               |
|Create #table  |               |
|---------------|               |
|Insert Data    |               |
|into #table    |               |
|---------------|               |
|return Dataset1|               |
|specific data. |               |
|===============|               |
                                |
-----------------               |
|Data-set 2     |---------------|
|===============|               |
|Input          |               |
|===============|               |
|@MyDate        |               |
|@ProcessorId   |               |
|===============|               |
|Variables      |               |
|===============|               |
|@StartDate     |               |
|@EndDate       |               |
|@Larges$Amount |               |
|---------------|               |
|Using #table   |               |
|return Dataset2|               |
|specific data. |               |
|===============|               |
                                |
-----------------               |
|Data-set 3     |---------------|
|===============|
|Input          |
|===============|
|@MyDate        |
|@ProcessorId   |
|===============|
|Variables      |
|===============|
|@StartDate     |
|@EndDate       |
|@reasonCode    |
|@Amount        |
|---------------|
|Using #table   |
|return Dataset3|
|specific data. |
|---------------|
|Drop #table    |
|===============|

This is how I believed it to work without having to use a stored procedure. Anyone tell me if I am doing something wrong?

Doing this my other datas-ets get an error similar to Query execution failed for data-set 'data-set2' invalid object name '#table'

Answer 1

So after some research I found out that using #temp tables created in one data set cannot be used in another data set. And using ##tables are not the way to go either as this will cause all kinds of issues when people try to run this report at or near the same time.

I was finally able to get what I needed by using a stored procedure to write the data I was going to use in other data sets into a staging table and before each run truncate the table ("TRUNCATE TABLE "). This will allow you to apply indexes and another performance modifications as needed.

Once execute your stored procedure in the first data set. Your data will be populated in the staging table and you can then call it from other data sets.

I hope this helps anyone that was trying to use temp tables in other data sets. Simple answer is its not possible.

Answer 2

I got the same like your problem and I put these lines of code in the beginning of my stored procedure:

IF 1=0 BEGIN
    SET FMTONLY OFF
END

Actually that statement will never executed, but it is just a work around solution.

I hope it helps.

Answer 3

This line solved it for me.

SET FMTONLY OFF

here is some info.

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-2017

I'm working on SSRS 2016 and the new report builder.