rajarshi_ghosh_05 (4/2/2013)
Guys, No luck. DBAs are not going to give me the permission to create table in tempdb so I come up with an idea to put both updlock and holdlock (outside the transaction) on the global temp table. However sometime it works and sometime it not. I dont understand about this strange behavior!! Maybe i need more testing on this. Will keep you posted.
Why are you resisting a permanent table in a user DB? Datetimestamp it and put an ID on it so the user can grab "their" messages, and an agent job to purge anything older than 121 minutes. Easy to do, without doing lock gyrations that arent recommended anyway.
Dont make this harder than it is.