|
|
You're in the
sql server programming
group:Job Activity Monitor tool
sql server programming:
can someone tell me what table or stored procedure that the Job Activity tool
uses to show the activity for the jobs on the db server? I'm asking because I'm creating a web form that list all db servers on the network and all the jobs under SQL Server Agent, and I need to show the status of the job that is running that was selected from the web form. Example: when the user selects a job they can run it, but I want to show this on my web form Jobname Step 1 processing. . . . Step 1 completed Succeeded Step 2 processing. . . . . Step 2 completed Succeeded and so one, I noticed that the Job Activity is showing this information. So how can I use the job activity monitor on my web form or use the stored procedure or table that the tool is using?
Correct, I'm using SQL 2005 though I don't see sysjobs_view in there, I do see
sp_help_jobactivity and sp_help_jobhistory but not the jobs_view. I'm able to run the job fine, I just want to show the samething that the job activity monitor is on my web form [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" wrote:
> I assume SQL Server 2005? I do something similar using these objects: > > msdb..sysjobs_view > msdb..sysjobactivity > msdb..sysjobhistory > > -- > Aaron Bertrand > SQL Server MVP > http://www.sqlblog.com/ > http://www.aspfaq.com/5006 > > > > > > "Tom" <Tom@discussions.microsoft.com> wrote in message > news:9F597F37-D40F-4E86-A11D-E96D32AA67CF@microsoft.com... > > can someone tell me what table or stored procedure that the Job Activity > > tool > > uses to show the activity for the jobs on the db server? I'm asking > > because > > I'm creating a web form that list all db servers on the network and all > > the > > jobs under SQL Server Agent, and I need to show the status of the job that > > is > > running that was selected from the web form. > > > > Example: > > when the user selects a job they can run it, but I want to show this on my > > web form > > Jobname > > Step 1 processing. . . . > > Step 1 completed Succeeded > > > > Step 2 processing. . . . . > > Step 2 completed Succeeded > > > > and so one, I noticed that the Job Activity is showing this information. > > So > > how can I use the job activity monitor on my web form or use the stored > > procedure or table that the tool is using? > > > >
I assume SQL Server 2005? I do something similar using these objects:
msdb..sysjobs_view msdb..sysjobactivity msdb..sysjobhistory -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 [quoted text, click to view] "Tom" <Tom@discussions.microsoft.com> wrote in message news:9F597F37-D40F-4E86-A11D-E96D32AA67CF@microsoft.com... > can someone tell me what table or stored procedure that the Job Activity > tool > uses to show the activity for the jobs on the db server? I'm asking > because > I'm creating a web form that list all db servers on the network and all > the > jobs under SQL Server Agent, and I need to show the status of the job that > is > running that was selected from the web form. > > Example: > when the user selects a job they can run it, but I want to show this on my > web form > Jobname > Step 1 processing. . . . > Step 1 completed Succeeded > > Step 2 processing. . . . . > Step 2 completed Succeeded > > and so one, I noticed that the Job Activity is showing this information. > So > how can I use the job activity monitor on my web form or use the stored > procedure or table that the tool is using? >
[quoted text, click to view]
> Correct, I'm using SQL 2005 though I don't see sysjobs_view in there Are you sure? I have it on my systems, here is the definition: CREATE VIEW sysjobs_view AS SELECT jobs.job_id, svr.originating_server, jobs.name, jobs.enabled, jobs.description, jobs.start_step_id, jobs.category_id, jobs.owner_sid, jobs.notify_level_eventlog, jobs.notify_level_email, jobs.notify_level_netsend, jobs.notify_level_page, jobs.notify_email_operator_id, jobs.notify_netsend_operator_id, jobs.notify_page_operator_id, jobs.delete_level, jobs.date_created, jobs.date_modified, jobs.version_number, jobs.originating_server_id, svr.master_server FROM msdb.dbo.sysjobs as jobs JOIN msdb.dbo.sysoriginatingservers_view as svr ON jobs.originating_server_id = svr.originating_server_id WHERE (owner_sid = SUSER_SID()) OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR (ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1) OR ( (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1) AND (EXISTS(SELECT * FROM msdb.dbo.sysjobservers js WHERE js.server_id <> 0 AND js.job_id = jobs.job_id))) [quoted text, click to view] > sp_help_jobactivity and sp_help_jobhistory but not the jobs_view. > > I'm able to run the job fine, I just want to show the samething that the > job > activity monitor is on my web form And you can that using queries against these three objects (I don't use the sp_help procedures you mention). -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006
I suggest you use SMO for this. Check out the namespace:
Microsoft.SqlServer.Management.Smo.Agent And classes etc like: Job Class .currentRunStatus JobExecutionStatus Enumeration -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Tom" <Tom@discussions.microsoft.com> wrote in message news:9F597F37-D40F-4E86-A11D-E96D32AA67CF@microsoft.com... > can someone tell me what table or stored procedure that the Job Activity tool > uses to show the activity for the jobs on the db server? I'm asking because > I'm creating a web form that list all db servers on the network and all the > jobs under SQL Server Agent, and I need to show the status of the job that is > running that was selected from the web form. > > Example: > when the user selects a job they can run it, but I want to show this on my > web form > Jobname > Step 1 processing. . . . > Step 1 completed Succeeded > > Step 2 processing. . . . . > Step 2 completed Succeeded > > and so one, I noticed that the Job Activity is showing this information. So > how can I use the job activity monitor on my web form or use the stored > procedure or table that the tool is using? >
Hi Aaron,
I can see this objects and I got the report of the jobs and the date time when suppost to run but how can I get if the last time the job success or failed? select v.originating_server, v.name, v.job_id, Max(a.run_requested_date) as requested_date, Max(a.next_scheduled_run_date) as next_run_date from msdb.dbo.sysjobs_view v inner join msdb.dbo.sysjobactivity a on v.job_id = a.job_id where v.enabled = 1 group by v.originating_server, v.name, v.job_id Tks JFB [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23wHAc30WHHA.4832@TK2MSFTNGP04.phx.gbl... >I assume SQL Server 2005? I do something similar using these objects: > > msdb..sysjobs_view > msdb..sysjobactivity > msdb..sysjobhistory > > -- > Aaron Bertrand > SQL Server MVP > http://www.sqlblog.com/ > http://www.aspfaq.com/5006 > > > > > > "Tom" <Tom@discussions.microsoft.com> wrote in message > news:9F597F37-D40F-4E86-A11D-E96D32AA67CF@microsoft.com... >> can someone tell me what table or stored procedure that the Job Activity >> tool >> uses to show the activity for the jobs on the db server? I'm asking >> because >> I'm creating a web form that list all db servers on the network and all >> the >> jobs under SQL Server Agent, and I need to show the status of the job >> that is >> running that was selected from the web form. >> >> Example: >> when the user selects a job they can run it, but I want to show this on >> my >> web form >> Jobname >> Step 1 processing. . . . >> Step 1 completed Succeeded >> >> Step 2 processing. . . . . >> Step 2 completed Succeeded >> >> and so one, I noticed that the Job Activity is showing this information. >> So >> how can I use the job activity monitor on my web form or use the stored >> procedure or table that the tool is using? >> > >
sysjobhistory will have run_status = 0 AND [message] LIKE 'The job failed.%'
where step_id = 0 and job_id = x. -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 [quoted text, click to view] "JFB" <help@jfb.com> wrote in message news:%23hVqPWBXHHA.3568@TK2MSFTNGP06.phx.gbl... > Hi Aaron, > I can see this objects and I got the report of the jobs and the date time > when suppost to run but how can I get if the last time the job success or > failed? > select v.originating_server, v.name, v.job_id, Max(a.run_requested_date) > as requested_date, > > Max(a.next_scheduled_run_date) as next_run_date > > from msdb.dbo.sysjobs_view v > > inner join msdb.dbo.sysjobactivity a > > on v.job_id = a.job_id > > where v.enabled = 1 > > group by v.originating_server, v.name, v.job_id > > Tks > > JFB > > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:%23wHAc30WHHA.4832@TK2MSFTNGP04.phx.gbl... >>I assume SQL Server 2005? I do something similar using these objects: >> >> msdb..sysjobs_view >> msdb..sysjobactivity >> msdb..sysjobhistory >> >> -- >> Aaron Bertrand >> SQL Server MVP >> http://www.sqlblog.com/ >> http://www.aspfaq.com/5006 >> >> >> >> >> >> "Tom" <Tom@discussions.microsoft.com> wrote in message >> news:9F597F37-D40F-4E86-A11D-E96D32AA67CF@microsoft.com... >>> can someone tell me what table or stored procedure that the Job Activity >>> tool >>> uses to show the activity for the jobs on the db server? I'm asking >>> because >>> I'm creating a web form that list all db servers on the network and all >>> the >>> jobs under SQL Server Agent, and I need to show the status of the job >>> that is >>> running that was selected from the web form. >>> >>> Example: >>> when the user selects a job they can run it, but I want to show this on >>> my >>> web form >>> Jobname >>> Step 1 processing. . . . >>> Step 1 completed Succeeded >>> >>> Step 2 processing. . . . . >>> Step 2 completed Succeeded >>> >>> and so one, I noticed that the Job Activity is showing this information. >>> So >>> how can I use the job activity monitor on my web form or use the stored >>> procedure or table that the tool is using? >>> >> >> > >
Got it Aaron....Tks
JFB [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OqjwOqBXHHA.1764@TK2MSFTNGP04.phx.gbl... > sysjobhistory will have run_status = 0 AND [message] LIKE 'The job > failed.%' where step_id = 0 and job_id = x. > > -- > Aaron Bertrand > SQL Server MVP > http://www.sqlblog.com/ > http://www.aspfaq.com/5006 > > > > > "JFB" <help@jfb.com> wrote in message > news:%23hVqPWBXHHA.3568@TK2MSFTNGP06.phx.gbl... >> Hi Aaron, >> I can see this objects and I got the report of the jobs and the date time >> when suppost to run but how can I get if the last time the job success or >> failed? >> select v.originating_server, v.name, v.job_id, Max(a.run_requested_date) >> as requested_date, >> >> Max(a.next_scheduled_run_date) as next_run_date >> >> from msdb.dbo.sysjobs_view v >> >> inner join msdb.dbo.sysjobactivity a >> >> on v.job_id = a.job_id >> >> where v.enabled = 1 >> >> group by v.originating_server, v.name, v.job_id >> >> Tks >> >> JFB >> >> >> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in >> message news:%23wHAc30WHHA.4832@TK2MSFTNGP04.phx.gbl... >>>I assume SQL Server 2005? I do something similar using these objects: >>> >>> msdb..sysjobs_view >>> msdb..sysjobactivity >>> msdb..sysjobhistory >>> >>> -- >>> Aaron Bertrand >>> SQL Server MVP >>> http://www.sqlblog.com/ >>> http://www.aspfaq.com/5006 >>> >>> >>> >>> >>> >>> "Tom" <Tom@discussions.microsoft.com> wrote in message >>> news:9F597F37-D40F-4E86-A11D-E96D32AA67CF@microsoft.com... >>>> can someone tell me what table or stored procedure that the Job >>>> Activity tool >>>> uses to show the activity for the jobs on the db server? I'm asking >>>> because >>>> I'm creating a web form that list all db servers on the network and all >>>> the >>>> jobs under SQL Server Agent, and I need to show the status of the job >>>> that is >>>> running that was selected from the web form. >>>> >>>> Example: >>>> when the user selects a job they can run it, but I want to show this on >>>> my >>>> web form >>>> Jobname >>>> Step 1 processing. . . . >>>> Step 1 completed Succeeded >>>> >>>> Step 2 processing. . . . . >>>> Step 2 completed Succeeded >>>> >>>> and so one, I noticed that the Job Activity is showing this >>>> information. So >>>> how can I use the job activity monitor on my web form or use the stored >>>> procedure or table that the tool is using? >>>> >>> >>> >> >> > >
[quoted text, click to view]
Aaron wrote: "sysjobhistory will have run_status = 0 AND [message] LIKE 'The job failed.%' where step_id = 0 and job_id = x." Does run_status correlate with the return code of sp_help_jobactivity? If so, then run_status = 0 indicates success. Is there a query I can run that will give a numeric failure code instead of relying on the "failed" string in the message field? From http://www.developmentnow.com/g/113_2007_2_0_0_938450/Job-Activity-Monitor-tool.htm Posted via DevelopmentNow.com Groups
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |