The last script builds a job that executes every Monday at 9am 🙂

Surely, someone's seen/heard of this issue before.

I do appreciate the work-around idea. But, I am still very curious as to why alerts aren't firing when the blocking threshold is reached.

server:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build...

It is the first alert on both the dev server and my local sql laptop. I have also restarted sql agent on both, and still same issue. I...

I deleted mine, executed your script, same issue. Doesn't matter how long I set it up to lock, as soon as it frees up the email is sent. ...

type: SQL Server Performance condition alert

Object: SQLServer:Locks

Counter: Lock Wait Time (ms)

Instance: _Total

Alert if counter: rises above Value: 1

Response: Notify operators (email works)

The alert technically works, but only after the locking...

The best way would be to create an email address table or add a column to an existing table for emails. The sp should go to the table.column for...

Thanks, that's what I thought. I appreciate the response.

Can't update original cte after the first select statement. Try adding a second cte something like this:

WITH CTE (ProblemID, Who, seq)

AS

(

SELECT X.problemId, X.Who,

ROW_NUMBER() OVER (PARTITION BY ProblemID...

Yep, replaced the part for 2k with a sql exec (@variable) and it worked, thanks a bunch!