tags:

views:

28

answers:

2

I have a ##table which can be accessed across all the sessions but sometimes I am getting error

There is already an object named '##table' in the database.

WHY and how to resolve it.

+3  A: 
There is already an object named '##table' in the database.

You would typically get this error if you are doing a CREATE Table statement which would obviously fail as '##table' already exists in the database.

Seems to me that maybe at some point in your code, the CREATE TABLE logic for this global table is being invoked again leading to this error.

Do the have the details of the exact statement that results in this error?

InSane
+1  A: 

Found an interesting reference here:

Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions). If two different sessions try the above code, if the first is still active, the second will receive the following:

Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named '##people' in the database.

I have yet to see a valid justification for the use of a global ##temp table. If the data needs to persist to multiple users, then it makes much more sense, at least to me, to use a permanent table. You can make a global ##temp table slightly more permanent by creating it in an autostart procedure, but I still fail to see how this is advantageous over a permanent table. With a permanent table, you can deny permissions; you cannot deny users from a global ##temp table.

Chris Porter
RE: "I have yet to see a valid justification for the use of a global ##temp table" I use global temporary tables when I want to be able to create a table in dynamic SQL and access it in the outer scope. By the way that article propagates the old myth that table variables are in memory and #temp tables on disc. There really isn't much (any?) difference between the two in that respect.
Martin Smith
I considered not including the 2nd paragraph as it didn't relate to the question directly. The first paragraph did discuss the exact issue described by Jeevan though.
Chris Porter