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 (https://blog.sqlauthority.com)
38 Comments. Leave new
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
drop table tempdb..#your_temp_table
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;
Sorry, I meant
IF OBJECT_ID(N’tempdb.dbo.#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!
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.
declare @sql nvarchar(max)
select @sql = isnull(@sql+’;’, ”) + ‘drop table ‘ + quotename(name)
from tempdb..sysobjects
where name like ‘#temp%’
–SELECT @sql
exec (@sql)
Msg 2714, Level 16, State 6, Line 1
how to solve this
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
I have the same problem with the code below please help me my friend , GOD bless you. create table factsalesorder
(productkey int not null references Dimproduct(productkey),
customerkey int not null references Dimcustomer(customerkey),
salespersonkey int not null references salesperson(salespersonkey),
orderdatekey int not null references Dimdate(datekey),
orderno int not null,
itemno int not null,
quantity int not null,
salesamount money not null,
cost money not null
CONSTRAINT [pk_factsalesorder] primary key nonclustered
([Productkey],[customerkey],[salespersonkey],[orderdatekey],[orderno],[itemno]),
)
go
use DemoDW
go
create table Dimproduct
(productkey int identity not null primary key nonclustered,
productaltkey nvarchar(10) not null,
productname nvarchar(50) null,
productdescription nvarchar(100) null,
productcategoryname nvarchar(50),)
go
create table Dimcustomer
(customerkey int identity not null primary key nonclustered,
customeraltkey nvarchar(10) not null,
customername nvarchar(50) null,
customeremail nvarchar(50) null,
customergeographykey int null,)
go
create table salesperson
(salespersonkey int identity not null primary key nonclustered,
salespersonaltkey nvarchar(10) not null,
salespersonname nvarchar(50) null,
storename nvarchar(50) null,
storegeographykey int null,)
go
create table DimDate
(datekey int identity not null primary key nonclustered,
datetaltkey datetime not null,
calenderyear int not null,
calenderquarter int not null,
monthofyear int not null,
[montname] nvarchar(15) not null,
[dayofmonth] int not null,
[dayofweek] int not null,
[dayname] nvarchar(15) not null,
fiscalyear int not null,
fiscalquarter int not null,
)
—fact table
create table factsalesorder
(productkey int not null references Dimproduct(productkey),
customerkey int not null references Dimcustomer(customerkey),
salespersonkey int not null references salesperson(salespersonkey),
orderdatekey int not null references Dimdate(datekey),
orderno int not null,
itemno int not null,
quantity int not null,
salesamount money not null,
cost money not null
CONSTRAINT [pk_factsalesorder] primary key nonclustered
([Productkey],[customerkey],[salespersonkey],[orderdatekey],[orderno],[itemno]),
)
go
I copied script of stored procedure onto another database.
But it shows Msg 2714, Level 16, State 3, Procedure procBlogAuditLogInsert, Line 13
There is already an object named ‘procBlogAuditLogInsert’ in the database.
I don’t see this object in my new location.
Can anybody help me please?
Sorry, your code did not work for me..IF OBJECT_ID(‘tempdb..##CLIENTS_KEYWORD’) IS NOT NULL
/*Then it exists*/
DROP TABLE ##CLIENTS_KEYWORD
This code does.
Thank you for addressing this question. Your fix does not work for SQL 2005, though. Even if I cut and paste your exact code into a clean SSMS query, I will still get the “already exists” error on the creation attempt unless the user account running under has the sa role. I’ve been battling this issue for several days, chasing triggers and stored procedures through our ERP system to find out what the problem might be. At the end of the day, the problem is the user must have sa role membership in order to correctly create/drop localized temporary tables, or at least that’s my perception at the moment, and also can no longer execute extended stored procedures that access the registry without it.
First, thank you for actually addressing the question. I’ve been reading posts for two hours of people thinking “you don’t have to drop temp tables – they drop themselves” is the answer. In my case, I’m not so much interested in dropping the temp tables as I am in creating them. But that cannot be done if the table “already exists”, and I can’t DROP it to get rid of it. Don’t get me wrong – your code that does the drop executes without error. But the create still fails after it is run, saying the file can’t be created because it already exists. If I grant sa role membership to the user, everything works perfectly.
Since CLR was implemented with 2005, I feel sure this is a SQL 2005 issue. Now I know there are going to be a bunch of “experts” tell me I should move beyond SQL 2005, so please no one waste my time with that. Sometimes, there is no choice, and this is one of those times, so using another version is not an option. I should also mention this is running on MS Server 2003 64-bit, and the SQL 2005 is 64-bit as well. Don’t even start on me about Server 2003, either. Again, I have no choice.
I am turning to you because you have obviously been around this a long time as I have (38 years for me), and your posts have often helped me. I’m hoping you’ve seen this particular issue in the past and can remember what resolved it. Thanks in advance for your help.
BEGIN
— Fix/Workaround/Solution:
IF OBJECT_ID(‘tempdb.dbo.#temp’, N’U’) IS NOT NULL
DROP TABLE dbo.#temp;
CREATE TABLE #temp(id INT )
SELECT name
FROM sys.tables
WHERE name LIKE ‘%#temp%’
END
GO