SQL Server Notification Services
SQL Server 2008 R2 to the rescue: new in 2008 R2 is something called Complex Event Processing, part of the StreamInsight runtime technology new to SQL Server 2008 R2. With SSNS (Notification Services) missing for a while now, it is a fantastic addition to the already powerful set of messaging options spanning from MSMQ through to BizTalk. Welcome back SQL Server!
PS: Thanks to Hovsep for pointing this out!