October 24, 2005 at 12:10 pm
Does this have to be run as an SP or can the code be fired from another application?
October 24, 2005 at 12:25 pm
I'm trying to get a sp solution because all my production jobs run as sp's and I'm looking to replace:
exec my_sp
with:
exec DeadLockSafe 'my_sp'
which is a minimally intrusive wrapper that can be used wherever 'my_sp' is called without any special programming. In the Agent Job Scheduler I use the "retry" option, but that only works in that environment.
October 24, 2005 at 12:36 pm
I see. I've never faced that problem and I can't think of a solution at the moment... maybe someone else will be in a better position to help you.
October 24, 2005 at 1:19 pm
Well, it appears that this is a fundamental problem with SQL Server 2000 because the deadlock monitor thread cannot distinguish between the caller (my "DeadLockFree" sp) and the called sp itself as it kills the entire spid associated with the deadlock, not just the deadlocking code itself.
However, "2005" provides the try/catch syntax which can be used to trap error 1205 and therefore resolves this problem.
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply