July 11, 2008 at 11:02 am
I am trying to delete table and getting following error.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#tableName', because it does not exist or you do not have permission.
I checked in the sysobjects table and it is there but whenever I use the following command I get error, table does not exists.
select * from dbo.#tableName
Looks like table is not there but it showing in the Management Studio.
July 11, 2008 at 11:06 am
Tables with a # at the beginning of the name are temporary tables. Don't prefix it with "dbo." and it should work okay.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 11:28 am
Actually, Some Analytical users are using this database and they create whatever they want.
I have removed the dbo prefix but still getting an error.
drop table #tablename
July 11, 2008 at 12:39 pm
balbirsinghsodhi (7/11/2008)
or you do not have permission.
balbirsinghsodhi (7/11/2008)
Actually, Some Analytical users are using this database and they create whatever they want.
local temp tables should normally clear up when out of scope although best practice is to kill it when you done with it
sounds like something could still have a handle on it, i would check the process activity and see who's doing what?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2008 at 2:22 pm
If you did not create the temp table, you cannot drop it. Only the connection that created the temp table can drop it.
The table will be deleted when it goes out of scope, or the connection that created it is closed
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 2:35 pm
It seems that you should close the conn, but also try to restart all services on the Instance where you are working!
:w00t:
July 11, 2008 at 2:38 pm
Although tables that begin with a # are normally temporary tables, it is possible to create a permanent table by enclosing the name with [ and ]
create table [#temp] (foo int)
go
drop table [#temp]
go
SQL = Scarcely Qualifies as a Language
July 11, 2008 at 2:50 pm
Dugi (7/11/2008)
It seems that you should close the conn, but also try to restart all services on the Instance where you are working!:w00t:
That's a tad extreme to get rid of a temp table. Why do you suggest restarting all services?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 2:55 pm
Carl Federl (7/11/2008)
Although tables that begin with a # are normally temporary tables, it is possible to create a permanent table by enclosing the name with [ and ]create table [#temp] (foo int)
go
drop table [#temp]
go
Yes, it can be done. I'd block access by anyone caught doing that, but it is possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 2:57 pm
Assuming this is a temp table, created by someone else, why are you trying to drop it anyway? There's generally no need to do so, and if they are still using it, it might cause huge problems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 3:11 pm
Carl Federl (7/11/2008)
Although tables that begin with a # are normally temporary tables, it is possible to create a permanent table by enclosing the name with [ and ]create table [#temp] (foo int)
go
drop table [#temp]
go
<headdesk>
Lovely as a trivia question, but....
[] are not supposed to change the meaning of a statement. They're supposed to just be delimiters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 3:28 pm
That is not a temp table. It's a real table with # in the begning.
July 11, 2008 at 3:48 pm
Wierd.
Try to rename it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 5:22 pm
the weird thing is I backed up the database from SQL 2000 and restore on SQL 2005. This table does not exists in SQL 2000 database but somehow got restored on 2005 server.
Try to delete but no success.
July 11, 2008 at 5:41 pm
I found a weird issue. I backed up database from sql 2000 server and restore on sql 2005 server. This weird table does not exists on sql 2000 but somehow restored on sql 2005 server.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply