is it possible to query a job?
-
of course i have hundreds of jobs. within each one is
an SMTP notification step.
one of the dba's have left the company, and i need to remove
this e-mail address from each and every job. of course doing this
through the EM will take for ever so i have beeen spending some
time trying to query the job-steps.
i am having some trouble with this.
is there some other way that this can be done?
thoughts?
_________________________
-
do a select * from tables in the msdb unless they are pretty big
then do a select * with a like for the email address in the column that holds it
can't remember the tables and columns right now
-
Hello,
You can query the system tables
sysjobs, sysjobsteps tables by having a join.
Hope this helps.
Lucky -
I don't know how much this will help, but here's a query I wrote long ago to do a quick informational dump on all jobs on an instance. For what you're doing it's at least a start, and will point you in the right direction.
You can directly update the msdb job tables--their not really system files--but any modifications made directly to the tables will not be "known" by SQL Agent. [Turn on SQL Profiler, do one by hand, and you'll see there's a lot of stuff going on under the hood]. A quick work-around to this is to stop and restart SQL Agent.
Philip
USE msdb
-- List relevant information about jobs on a per-job basis
SELECT
sj.job_id
,sj.name
,sjt.TotalSteps
,sj.enabled
,sj.notify_level_eventlog EventLog
,sj.notify_level_email EMail
,isnull(mailOp.name, '-----') MailOp
,sj.notify_level_page Pager
,isnull(pageOp.name, '-----') PageOp
,sj.notify_level_netsend NetSend
,isnull(netOp.name, '-----') NetOp
,TSQLSteps
,CmdExecSteps
,ActScrptSteps
,EnabledSchedules
,DisabledSchedules
from msdb..sysJobs sj
inner join msdb..sysCategories sc
on sc.category_id = sj.category_id
left outer join (select
job_id
,sum(case enabled when 1 then 1 else 0 end) EnabledSchedules
,sum(case enabled when 0 then 1 else 0 end) DisabledSchedules
from msdb..sysJobSchedules
group by job_id
) sjs
on sjs.job_id = sj.job_id
left outer join (select
sjt.job_id
,count(*) TotalSteps
,sum(case sjt.subsystem when 'ActiveScripting' then 1 else 0 end) ActScrptSteps
,sum(case sjt.subsystem when 'CmdExec' then 1 else 0 end) CmdExecSteps
,sum(case sjt.subsystem when 'TSQL' then 1 else 0 end) TSQLSteps
from msdb..sysJobSteps sjt
group by sjt.job_id
) sjt
on sjt.job_id = sj.job_id
left outer join msdb..sysOperators mailOp
on mailOp.id = sj.notify_email_operator_id
left outer join msdb..sysOperators pageOp
on pageOp.id = sj.notify_page_operator_id
left outer join msdb..sysOperators netOp
on netOp.id = sj.notify_Netsend_operator_id
-
excellent feed back! thanks to all for this
_________________________
-
In future the way to avoid this would be use "Distribution List" so that all the DL's can be maintained on the mail server and you don't have to modify anything on the SQL side.
Thanks
Sreejith
-
If you use this widget from Gert D. you can script out all the jobs on a server. Using your perferred text editor you can do a global replace and recreate the jobs.
http://sqldev.net/sqlagent/ScriptJobs.htm
Two warnings, pne the tool is a little clunky so practice on a dev server. Two, you'll loose all your history since this process will do a drop old and create new.
good luck
-
Sweet! If that works I'll be golden!
Thanks!
_________________________
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply