rajarshi_ghosh_05 (4/2/2013)

---

DiverKas (4/2/2013)

---

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.

Actually all the people who are architect of this database are from Oracle background (me as well). Without knowing the limitation of sql server temp table the database design was made and approved by client. Now if we want to create any new table that needs to be passed through lot of process which we want to prevent and that is the reason for so much complexity.