Latest version: 11/14/2012.
(last update 2013/07/31 - looks like it's alive and well in 2014!)
I specifically went with a grandiose title to get people interested in this - it's (Event Notifications) a wonderful feature that very few people use (or even know about!), and now that I use it I find it absolutely vital. I hope I get across how great this feature is, especially since it works from SQL Server 2005 - 2014.
Below is a link to my slide deck, demos, scripts and reports.
The end result is a table that keeps track of code changes across potentially hundreds of servers. I've got it running on more than
One of the takeaways: once you have it running, you can run the following report in SSMS - right-click on an object, choose the RDL above via "Custom Report...", and it'll show details. Changes, index maint (if a table), etc. Works on SP/fxns, tables, databases, and servers - click on an object, it'll provide details on that object. Click on a DB, it'll give you all changes for that DB. Click on Server...same thing.
This works in SSMS 2005/2008/2012. And let me state for the record, it sucks that you have to develop it in BIDS 2005 in order for SSMS 2008 to view it properly.
I'll be tweaking this report; it's bare-bones and not particularly pretty, but is a really handy way to track down changes.
A couple examples: (apologies for the size, but otherwise you can't easily see the commandtext which shows the actual commands executed - click to enlarge)
In order for the reports to work in SSMS, you will need to create a linked server on each monitored server, pointing at the server that holds EventNotificationRec, with the name ENRepository (collation compatible true and RPC OUT on). This way when you right-click on an object it will send the server/database/objectname across on each server - this is needed because SSMS does not let you use a different data source.
The version of the report shown this evening is EN_ReportSP. I will be tweaking it, as well as the other code. Watch this space.
Please feel free to use, share, improve. Please contact me for commercial use (hey, a guy can hope).