5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeevan Bhatt
  • 5,881
  • 18
  • 54
  • 82

3 Answers3

8

Found an interesting reference (outdated URL referencing what is now a malicious website removed.):

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.

Joshua Schlichting
  • 3,110
  • 6
  • 28
  • 54
Chris Porter
  • 3,627
  • 25
  • 28
  • 2
    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 Oct 08 '10 at 19:16
  • 2
    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 Oct 08 '10 at 19:48
3

So the WHY part has been answered and here is how to resolve it:

Do a check to see if the temp table exists before creating it:

    if object_id('tempdb..##table') is null begin
        --create table ##table...
    end

I found a pretty interesting post about how to check the existence of a temp table from Googling http://sqlservercodebook.blogspot.com/2008/03/check-if-temporary-table-exists.html

Neal Xiong
  • 1,067
  • 10
  • 8
  • Is this thread-safe ? How does this avoid race condition when other session is trying to do the same and somewhere after if creates that table before first session creates it ? – alpav Feb 18 '14 at 19:18
  • @alpav It doesn't avoid race condition so if two threads happen to create the temp table at the same time, one of them will fail. – Neal Xiong Feb 28 '14 at 22:27
  • Given that SQL Server is an ACID compliant database, I'd assume that the race condition is alleviated via the locking mechanism on the underlying system tables that govern object names. That assumption may be misplaced, but if so I'd love to see a repro where this fails. – Ben Thul Oct 14 '20 at 20:21
3
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?

Jagmag
  • 10,283
  • 1
  • 34
  • 58