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

 

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

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 (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL TempDB
Previous Post
SQLAuthority News – SQL SERVER 2008 – Updated Brochure Available for Download
Next Post
SQLAuthority News – Top 10 Strategic Technologies for 2009

Related Posts

38 Comments. Leave new

  • 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?

    Reply
  • Hi Pinal,
    It can be also work with below code…

    IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
    DROP TABLE #temp

    CREATE TABLE #temp….

    -Abhijit’

    Reply
    • @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

      Reply
    • @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

      Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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!

    Reply
  • 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
    )

    Reply
  • Hitesh Ladva
    July 1, 2009 3:23 pm

    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

    Reply
  • 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

    Reply
  • 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!

    Reply
  • 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

    Reply
  • Thanks, This article helped answer my questions about this error.

    Reply
  • 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

    Reply
  • 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?

    Reply
  • @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

    Reply
  • nevermind.. i tried killing the connection – it only dropped the temp table, not the named constraint

    Reply
  • 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

    Reply
  • 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%’

    Reply
  • 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

    Reply
  • This code works. Sourced from:

    Code Snippet
    if exists (
    select * from tempdb.dbo.sysobjects o
    where o.xtype in (‘U’)
    and o.id = object_id(N’tempdb..#tempTable’)
    )
    BEGIN
    DROP TABLE #tempTable;
    END

    Reply

Leave a Reply