I'm having a bit of trouble with locking:
Say I have 2 stored procedures, P1 and P2.
P1 calls P2, and I want the whole thing to abort after a certain length of time, but I'm not having any luck.
Here's the basic thing that I have:
CREATE PROCEDURE P1
SET NOCOUNT ON
SET LOCK_TIMEOUT 2000
--Do some stuff
-- Do some more stuff
What I want to happen is for the first transaction in P1 to run, which will take less than 2 seconds, then run P2 in the second transaction. P2 is a really long running SP, and I want it to stop if it doesn't get the whole way through.
However, despite setting LOCK_TIMEOUT, it still just keeps on running when I issue:
in query analyser, taking over 3 minutes.
Anyone know how I can fix this?
I am a loud man with a very large hat. This means I am in charge