Stopped counting many moons ago.

Wow, thanx for this one.  This is really not an obvious place to look for the problem .

This can be quite simple with any level of failure. You can add another column like TagDate and set it to getdate() when setting the tag in place.

Then have a...

I can't seem to get this column to show any data.  Right now all I can offer is to parse the command text for object names...  For from the best...

SCOPE_IDENTITY() works in 2000 so you can use it.

The only questions I have is what do you do if the sp runs (with delete) and for some bizarre reason the app...

Care to elaborate a bit more on this one?

Thanks for this interesting concept.  I'm not sure I'd use it over the uniqueidentifier. 

I like to have as little transactions as possible but I'll be sure this keep this...

That's also why I threw in the output parameters in there.  If for any bizarre reason you get disconnected, then you can still continue the batch.  But then again I...

No you can only check if the table exists in tempdb..#tablename to see if you own it.  Not much else you can do, and for good reasons.

I'll take Jeff's word for it.  I don't have enough experience in high transaction environement to comment on deadlocks.

The only change I would make is add a second sp.

sp 1 tags...

As the only process seems to be a SELECT, I would do something, like the following, so there is only one commit:

I would not assume that if I were you. ...

Just to complete... The temp table will also be dropped when the sp in question ends, or when the table is explicitly dropped in the sp.

Again no need to drop the...

Well that is another problem altogether.  You need to figure out the normal max required size of temdb at any given time of the week.  Then you need to make sure...

You can't drop a temp table from someone else connection like that.

The only options you have all have one thing in common : kill all active connections on the server. ...

NP.

Also you'll have tokeep in mind that a single quote in the user name will break the query.  To avoid this situation you have 2 options

1 - use replace around...