drop temp table owned in another session

Question

drop temp table owned in another session

snomadj

SSCarpal Tunnel

Points: 4491
More actions
March 31, 2016 at 3:31 am

#306086

Almost silly question that I'm curious about.
Can I drop a local temp table owned by myself in another session?
I can't just use drop because I get the 'does not exist or you do not have permission' error.
When I'm coding I generally have multiple windows open & cannot drop local temp tables I created in other windows. I know they're mine. It's unimportant in a way as I can just bob back to my original session & drop the table.
This works with global temp tables.
Presumably it's something to do with the numbered suffix on local temp tables? Either way I can't suss it.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

snomadj (3/31/2016)
Can I drop a local temp table owned by myself in another session?

No. Temp tables are session-scoped, you cannot access a temp table created in another session, not to read it, not to drop it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110208 More actions · Answer 2

GilaMonster (3/31/2016)
snomadj (3/31/2016)
Can I drop a local temp table owned by myself in another session?
No. Temp tables are session-scoped, you cannot access a temp table created in another session, not to read it, not to drop it.

Actually, you can:

KILL @SPID

😉

_____________
Code for TallyGenerator

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Sergiy (3/31/2016)
GilaMonster (3/31/2016)
snomadj (3/31/2016)
Can I drop a local temp table owned by myself in another session?
No. Temp tables are session-scoped, you cannot access a temp table created in another session, not to read it, not to drop it.
Actually, you can:
KILL @SPID
😉

And you can restart your computer by formatting it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 4

Thanks all. I get the gist. Didn't know if there was a:

DROP TABLE [sessionID, databaseName, #tableName] command I didn't know about.

Appreciate your time to respond. Have nice weekends!

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

snomadj (4/1/2016)
Thanks all. I get the gist. Didn't know if there was a:
DROP TABLE [sessionID, databaseName, #tableName] command I didn't know about.
Appreciate your time to respond. Have nice weekends!

If you have a process where you need sessions to talk to the same Temp Table, you could use a Global Temp Table to do such things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Luis Cazares (4/1/2016)
Sergiy (3/31/2016)
GilaMonster (3/31/2016)
snomadj (3/31/2016)
Can I drop a local temp table owned by myself in another session?
No. Temp tables are session-scoped, you cannot access a temp table created in another session, not to read it, not to drop it.
Actually, you can:
KILL @SPID
😉
And you can restart your computer by formatting it.

Not really.

Formatting a hard drive does not actually restart the computer.

🙂

_____________
Code for TallyGenerator

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Sergiy (4/3/2016)
Luis Cazares (4/1/2016)
Sergiy (3/31/2016)
GilaMonster (3/31/2016)
snomadj (3/31/2016)
Can I drop a local temp table owned by myself in another session?
No. Temp tables are session-scoped, you cannot access a temp table created in another session, not to read it, not to drop it.
Actually, you can:
KILL @SPID
😉
And you can restart your computer by formatting it.
Not really.
Formatting a hard drive does not actually restart the computer.
🙂

When it's the system hard drive it needs to restart to change the OS.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2