Who gets notified?

Question

Post reply

Who gets notified?

Smellypom

SSC Enthusiast

Points: 158
More actions
November 11, 2010 at 8:20 pm

#142618

Hi There,
I'm trying to generate a report that shows all enabled agent jobs on an instance, and of these which if any operator is notified of job success / failure / completion.
It's not a problem for the agent jobs themselves where the detail is in the Notifications section, I'm getting this data from a combination of msdb.dbo.sysjobs and msdb.dbo.sysoperators.
My problem is getting the details for the maintenance plans where these have 'Send report to an email recipient' selected with an Agent operator selected under the reporting and logging section.
I don't seem to be able to find anywhere where this information is stored in msdb. I've checked in these tables and views:
msdb.dbo.sysmaintplan_log
msdb.dbo.sysmaintplan_logdetail
msdb.dbo.sysmaintplan_subplans
msdb.dbo.sysmaintplan_plans
but have run out of other options
I have a number of servers to validate that are located on each side of the world and opening up each and every maintenance plan is going to take an age so I'm hoping against hope that there is an alternative.
Any thoughts anyone?
Thanks in advance,
Smellypom

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Did you have a look at this?

select * from sysdtslog90

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Smellypom SSC Enthusiast Points: 158 More actions · Answer 2

Yep, Null output - think that relates to 2000 packages run from within 2005 and later.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I believe this information is only stored in the SSIS package. You can use the object model in .NET to walk through the package and pick that information out.

CEWII

Smellypom SSC Enthusiast Points: 158 More actions · Answer 4

Thanks Elliott,

.NET is a little over my head at the moment, I did however stumble across a post by Adam Haines (http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f4c858a2-992a-410b-97b4-8bf63ee5138f) which pointed me in the right direction using XQuery.

If you've any examples of how I could have done this in .NET I'd be interested to try to get my head round it but in the mean time panic is over as I have the info I need.

Smellypom