Very nice, Geoff.

Here's a script that generates a deadlock to force the alert. Good to test the job.

Shamelessly lifted from some other forum, and since it worked for me, I am passing on the love.

--First run this code to create the tables and populate with data.

CREATE TABLE ##temp1 (Col1 INT)

CREATE TABLE ##temp2 (Col1 INT)

INSERT ##TEMP1

SELECT 1 UNION SELECT 2 UNION SELECT 3

INSERT ##TEMP2

SELECT 1 UNION SELECT 2 UNION SELECT 3

--Execute this in SSMS window #1

BEGIN TRAN

UPDATE ##temp1 SET Col1 = 4 WHERE Col1 = 3

--Delay long enough to lock ##temp1 in this process

--and allow ##temp2 to be locked in other process

WAITFOR DELAY '0:0:10'

--This is holding lock on ##temp1 waiting for ##temp2 to be released

UPDATE ##temp2 SET Col1 = 4 WHERE Col1 = 3

COMMIT TRAN

--Paste this code in another SSMS window and execute it

--SSMS window #2

BEGIN TRAN

UPDATE ##temp2 SET Col1 = 4 WHERE Col1 = 3

--Delay long enough to lock ##temp2 in this process

--and allow ##temp1 to be locked in other process

WAITFOR DELAY '0:0:10'

--This is holding lock on ##temp2 waiting for ##temp1 to be released

UPDATE ##temp1 SET Col1 = 4 WHERE Col1 = 3

COMMIT TRAN

/*

SELECT * FROM ##temp1

SELECT * FROM ##temp2

SELECT @@trancount

DROP TABLE ##temp1

DROP TABLE ##temp2

*/

Cheers