Re-use of ##Global Temporary Table in additional SSRS Datasets

Question

We currently have multiple Datasets in the same report that are going to use the same ##Global Temporary Table. Should the ##Global Temporary Table be defined in the first Report Dataset listed in Microsoft Visual Studio so then it can be re-used by subsequent Datasets after the first one? Or do we still need to check the Use single transaction when processing the queries within the Data Source Properties? Just not sure how SSRS and the order that SSRS and the .rdl will process Datasets and the creation of a #Temporary Table and/or a ##Global Temporary Table. Thanks in advance for your review and am hopeful for a reply.

Answer 1

Are you trying to have SSRS datasets based on queries using the same global temporary table in SQL Server? That itself seems like an anti-pattern.
What's the reason for the global temporary table? (I've used SQL Server for over 30 years and have seen global temporary tables used about twice - and in both those cases, for the wrong reason). Not saying it's necessarily wrong, but there is likely a better way to achieve what you're trying to do.

Answer 2

Hi @Bobby P，

Check out here:

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.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.