Best practices to name a ##Global Temporary Table used within a SSRS Datset SQL Server Stored Procedure

Question

Is there a Best Practices in naming a ##Global Temporary Table in a SQL Server Stored Procedure which is used within a SSRS Dataset so that the creation and use of the ##Global Temporary Table won't step on other people that may actually try and attempt to run the same report at around the same time? Should the ##Global Temporary Table be further qualified by the requesting user? And how would I go about doing that?

Thanks for your review and am hopeful for a reply.

Answer 1

Hi ITBobbyP,

In SQL Server, global temporary tables are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server. If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For more information, please refer to Temporary Tables section in this article: https://technet.microsoft.com/en-us/library/ms177399.aspx .

To create a stored procedure using global temporary table, you can refer to links below:

Temporary Tables in SQL Server
SQL Server - Global temporary tables

Then you can create a dataset, set Query type as Stored Procedure to execute the procedure.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

---

Qiuyun Yu
TechNet Community Support

Answer 2

Did this really answer your question? I am having the same issue with blocks in my DB because multiple people are running the report.