Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2010-11-28 : 18:52:06
|
| Hello,I just setup a new installation of sql server 2005 express with the adventureworks 2005 database. I created a temp table on that database, but now it is not letting me drop the temp table. It says:"Cannot drop the table '#TMP_TBL', because it does not exist or you do not have permission."I know it exists because I see it in the tempdb system database and I used object_id to test its existence and it does exist. So, it does exist. So, I'm guessing I don't have permissions. The question is why and how do I setup myself up so I have permissions to drop this table?Thank you. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-28 : 19:06:27
|
http://www.sqlteam.com/article/temporary-tables
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-28 : 19:08:14
|
| A temp table gets droped automatically when the connection closes - restart the server if necessary.You can't drop it because you are probably trykng to access it on another connection.Trycreate table #TMP_TBL (i int)You should now see 2 temp tables with that name (but a different extension) in tempdb.You should be able to drop the one you have created or just closee the connection and it will drop automatically.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2010-11-28 : 20:05:23
|
| Well, I was using it in the context of a bit of code I was working on; so, I needed to drop it at that instant. But I figured out what the problem was. Thanks for the help, though. |
|
|
|
|
|
|
dropping temp table created in adventureworks