SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Recently, one of my regular blog readers emailed me with a question concerning the following error:

Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

This reader has been encountering the above-mentioned error, and he is curious to know the reason behind this. Here’s Rakesh’s email.

Hi Pinal,
I’m a  regular visitor to your blog and I thoroughly enjoy your articles and especially the way you solve your readers’ queries. I work as a junior SQL developer in Austin. Today, when I started to create a TSQL application, I detected an interesting scenario.

It is a common practice for many of us to use the following statements to create a new object.But when I used local temporary table, i.e.

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name='#temp')
DROP TABLE #temp
CREATE TABLE #temp(id INT )

An error was displayed as:  Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

On running the statement
SELECT *
FROM
sys.tables


I see the object under name column in sys.tables as ,
#temp_______________________________________________________________________________________________________________0000000002E1

As far as I can discern, my SQL Server is writing the above entry instead of #temp. Therefore,  I’m getting the error as table already exists – There is already an object named ‘#temp’ in the database. Can you please explain me why this happens to local temporary table?

Thanks and Sincerely Yours,
Rakesh

Rakesh, your question is noteworthy. Let’s look at the solution first and then we will look into the behavior.

Fix/Workaround/Solution:

In case of Temporary tables, run following T-SQL. Please note do not run this for any physical tables. Additionally, please note to replace #temp with yourtemptable name.

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(id INT )

However, make sure not to run this for physical tables. Additionally, please note to replace #temp with yourtemptable name.
Local temp tables can be created using hash (#) sign prior to table name. They are visible only in current connection.. When connection is dropped its scope ends as well. It is possible to create and use local temp table with the same name simultaneously in two different connections. In order to allow this behavior SQL Server suffixes name of the local temp table with incremental hex digit, which is reset when SQL Services are restarted. It is because of this reason that when looking into sys table it has to compared using LIKE and ‘%’.

Let me create few temporary tables, and then we will observe how hex numbers are suffixed to local temporary tables.

BEGIN
IF
EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(id INT )
SELECT name
FROM sys.tables
WHERE name LIKE '%#temp%'
END
GO 10

 

Rakesh and my other readers, I encourage all of you to send me interesting and challenging questions that intrigue you as well as observations that you would like to share. As always, I will try my best to answer all your questions. Also, do send me your valuable opinions regarding this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

33 thoughts on “SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

  1. Luci explanation, thanks.

    But this reminds me of the incomprehensable error messages that sql server produces: what do the Level, State and Line in the error message mean? And is there any way to relate these items to the SQL code that produced them?

    • @Abhijit

      I’ve tried your code and it works, but it fail when there’s a 2byte character (e.g. Korean) in the name of temporary table.

      So, to avoid an exception, I think it will be good to use “IF EXISTS” statement instead.

      Example:
      IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N’[TempDB]..[#NewNEW_PROCESS_mbr엔티티]‘))
      DROP TABLE [#NewNEW_PROCESS_mbr엔티티]

      -Alex

    • @Abhijit

      I’ve tried your code and it works, but it fail when there’s a 2byte character (e.g. Korean) in the name of temporary table.

      So, to avoid an exception, I think it will be good to use “IF EXISTS” statement instead.

      Example:
      IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N’[TempDB]..[#NewNEW_PROCESS_mbr엔티티]‘))
      DROP TABLE [#NewNEW_PROCESS_mbr엔티티]

      -Alex

      ============================================

      Just ignore my above comments, since I just identified that there’s no problem with Abhijit’s query even though there’s a 2 byte character in the temporary table.
      The reason why I had a problem is that I missed N before the table name :)

      Incorrect:
      IF OBJECT_ID(‘[TempDB]..[#NewNEW_PROCESS_mbr엔티티]‘)) IS NOT NULL

      Correct:
      IF OBJECT_ID(N’[TempDB]..[#NewNEW_PROCESS_mbr엔티티]‘)) IS NOT NULL

  2. Abhijit,

    I think that using OBJECT_ID(’tempdb..#temp’) IS NOT NULL is one and only reasonable way in these two cases.

    #temp table exists in current session, so when you use OBJECT_ID you will get ID (or not, if not exists) of your own object from current session.

    Executing:

    IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘#temp%’)
    DROP TABLE #temp

    you may get:

    Msg 3701, Level 11, State 5, Line 2
    Cannot drop the table ‘#temp’, because it does not exist or you do not have permission.

    …there might be temporary table(s) with name ‘#temp’ but not in your current session.

  3. Thanks a lot for the reply pinal.
    And also the above code should run on tempdb database . As temp tables are created in tempdb database.

  4. Pinal,
    Few weeks ago, you have posted how to insert image in sql server. And i’m still waiting for your answer for how to retrive image from sql server . I’m wondering is there any way we can retreive without using .NET CLR coding.

    Thanks,
    Rakesh

  5. So can you forceably drop those tables? When I try to, I get the “Cannot drop the table ‘#temp’, because it does not exist or you do not have permission” error that the commentor above mentioned.

    I also have a constraint that I added to the temp table in the stored procedure. That constraint is now viewable in tempdb..sysobjects but I can not access it! The name of the constraint does not have the “_______hex” suffix on it. Because of this my sproc now fails because it tries to create the constraint but it already exists – although I can’t get to it to drop it!

    Yes I can check if it exists and not add it, but the constraint is attached to a “dead” temp table that I can not drop.

    Thanks!

  6. In sql 2005 or sql 2008, i use a try..catch loop to try and drop the table before creating it

    begin try
    drop table #temp
    end try
    begin catch

    end catch

    create table #temp (
    col1 int
    )

  7. Hi,

    I get the Msg 2714, Level 16, State 3 error message when running a script using SQLCMD, if i run the script directly in SQL Server 2008 it works without any problems.

    The script file contains the following
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[usp_GetLoanBalanceHistory]‘) AND type in (N’P’, N’PC’))
    DROP PROCEDURE [dbo].[usp_GetLoanBalanceHistory]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[usp_GetLoanBalanceHistory]‘) AND type in (N’P’, N’PC’))
    BEGIN
    EXEC dbo.sp_executesql @statement = N’CREATE PROCEDURE [dbo].[usp_GetLoanBalanceHistory](@TransactionId int)

    AS
    BEGIN
    SET NOCOUNT ON;

    SELECT [LoanBalanceSnapShotId]
    FROM [dbo].[LoanBalanceSnapShot]
    WHERE [TransactionId] = @TransactionId;

    END


    END
    GO

    Also, sometimes the script will NOT generate an error but the proc still doesn’t appear in the database.

    A couple of the My DBAs have had a look at this and they are baffled as to the cause.

    Thank you for your time and assitance.
    Hitesh

  8. Hi pinal

    I need solution for this fix

    Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

  9. Pinal,
    I recently changed to a DB developer, having only worked with “Select” for Access and .NET in the past. In this time I have used your solutions more time than I can tell you. This just saved my skin again. Thank you!

  10. hello,
    I would like to send you couple of my stored procedures where i have some strange problem. The code is really long can i get your email id so that i can attach them for you to have a look?
    thanks
    Srividhya Sundaram

  11. Msg 2714, Level 16, State 6, Line 4
    There is already an object named ‘employee’ in the database.
    when i fire the qury it does not give the proper answer plz give me answer soon
    whatt is the solution on that type error

  12. Hi Pinal,
    How about this scenario:

    create procedure proc1 (@a int)
    as
    begin
    if @a=1
    create table #temp(a1 int)
    else
    create table #temp(a1 int)
    drop table #temp
    end

    I also get this error : “There is already an object named ‘#temp’ in the database.”

    I know this is due to parsing or a kind of bug… but know the exact reason, please clarify?

  13. @DOUG FL,
    u can try to find the connection that is still using that temp table, then kill it.
    sp_who2 spid
    dbcc inputbuffer (spid)
    kill spid

  14. Unfortunately I haven’t been able to get this to work. I had to settle for the following which drops the temp table and ignores the error. SS 2008 R2

    DECLARE @NOTHINGVAR VARCHAR(10)
    DROP TABLE #result
    IF @@ERROR 0
    BEGIN
    SET @NOTHINGVAR = ”
    END

  15. Hi Pinal,
    Here is one interesting error i am continue to receive.
    I am using sql server 2005. I am trying to create one new table but whenever i ran the below script i am getting the following error.
    Msg 2714, Level 16, State 4, Line 1
    There is already an object named ‘TV_BookEMPDMAP_BKP_PROD’ in the database.
    I make sure there is no such table exists in the database and i even tried to change the name of table but still no luck.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[TV_BookEMPDMAP_BKP_PROD](
    [BOOK] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [EMPLOYEE [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [TV_BookEMPDMAP_BKP_PROD] PRIMARY KEY CLUSTERED
    (
    [BOOK] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    I used below query to verify such table does not exists.
    select * from sys.tables where name LIKE ‘TV_BookEMPDMAP_BKP_PROD%’

  16. Hello i found this post and I hope that I can find a solution asap. I am working on an ASP.net project on Visual Studio 2010, I am trying to set up a testing environment from an existing database and then I will try to restore all the data with a .bak file. Anyway when I try to run the script to create a table it gives me the same error that Rakesh got:
    Msg 2714, Level 16, State 6, Line 1
    There is already an object named ‘Applicants’ in the database.

    This is the code that I am trying to run and I validate it before running it:

    CREATE TABLE [dbo].[Applicants](
    [APPLICANT_ID] [int] IDENTITY(1,1) NOT NULL,
    [FIRST_NAME] [varchar](50) NOT NULL,
    [MIDDLE] [varchar](50) NULL,
    [LAST_NAME] [varchar](50) NOT NULL,
    [EMAIL] [varchar](50) NULL,
    [PHONE] [varchar](30) NULL,
    [STREET] [varchar](100) NULL,
    [CITY] [varchar](50) NULL,
    [STATE] [varchar](20) NULL,
    [ZIP] [varchar](20) NULL,
    [APPLICANT_DATETIME] [datetime] NOT NULL,
    CONSTRAINT [PK_Applicants] PRIMARY KEY CLUSTERED
    (
    [APPLICANT_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    This is part of a bigger script that completely fails when I validate saying that the database does not exist, but the database is there and I am connected.

    Please let me know if you see anything wrong with the code, or if I have to do something else before I run the script, I am a newbie at this.

    Thanks.

    Manu

  17. Hi Pinal,

    In my TempDB, I have got some temp tables #T______________HexNumbers.
    Some of them have been created back in September 2011, but were not dropped. I have found the procedures also where they were used, but they had dropped statements . Still the table are present .
    How will I drop these tables?
    The table names in tempdb..sysobjects are
    #T__________________________________________________________________________________________________________________000000002450
    #T__________________________________________________________________________________________________________________00000000268B
    #T__________________________________________________________________________________________________________________0000000029E2
    #T__________________________________________________________________________________________________________________00000000205D
    #T__________________________________________________________________________________________________________________00000000219A

    Thanks,
    Harsha

  18. Do NOT use the original suggestion. It’s needlessly complex, and worse it’s broken. It’s will be confused by temp tables with the same or similar names in different sessions, all of which will have rows in tempdb.sys.tables. Instead just KISS

    IF OBJECT_ID(N’#temp’) IS NOT NULL
    DROP TABLE #temp;

      • I have had to use this option to run:

        IF OBJECT_ID(‘tempdb..#TBL’) IS NOT NULL”

        The original version of the post I don’t run in my script.
        I’m using SQL Server 2008.

        Regards!

  19. Pinal, thank you so much for this information. To vastly experienced SQL guys, this may seem like 101 stuff, but for a lot of us who may have worked many years, but in a narrow scope, your articles are absolute lifesavers.

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

  21. declare @sql nvarchar(max)
    select @sql = isnull(@sql+’;’, ”) + ‘drop table ‘ + quotename(name)
    from tempdb..sysobjects
    where name like ‘#temp%’
    –SELECT @sql
    exec (@sql)

  22. Dave,

    I created one global temp table like this..
    create table ##gtemp(id int)
    insert into ##gtemp values(1)
    begin tran
    select * From ##gtemp

    Here my transaction is not ended its still running …..
    and i went for new session /connection ran the query as below

    if OBJECT_ID(‘tempdb..##gtemp ‘) is not null
    drop table ##gtemp

    whats the thing is the table has been dropped…
    What my question is i suppose in my programming if i create any global table some other user will drop that table i will loss my table data..?..if loss what is the user for that global table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s