0

We have set of global temporary tables which are created in SP and filled. And that's kind of main sp which is called from application.

Now say i am the first user who call's the main SP the global temporary tables get created and everything works fine. But some times (might be when multiple users are accessing the main SP) the tables get dropped and get a error message saying invalid object name

Anybody else faced the same issue with global temp tables getting dropped ??

Thanks in Advance !!

user3724852
  • 1
  • 2

1 Answers1

0

You can make a check whether the table exist before creating the temp table.
Instead of global temp table, if the table used only in the scope of the stored proc, prefer local temp tables or table variable

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table

All other local temporary tables are dropped automatically at the end of the current session

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended

Before creating the local temp table,

IF OBJECT_ID('tempdb..#local_temp_table') IS NOT NULL DROP TABLE #local_temp_table
CREATE TABLE #local_temp_table(id int)

or table variable

declare @table_var table(id int)
Praveen
  • 8,945
  • 4
  • 31
  • 49
  • No we need a way where in the temp tables can be used across the application by other users. Hence we opted for global temp tables. But inspite of the fact that global temp tables wont be dropped until and unless there is no active session its still getting dropped even if there is a single user on the environment even he gets the error message that "Invalid object [Global Temp table name]" – user3724852 Oct 14 '15 at 06:06