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)
pinaldave
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?
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’
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.
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.
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
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!
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
)
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
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
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!
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
Thanks, This article helped answer my questions about this error.
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
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?