HELP! From Job tasks to ASP

Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status
,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I c
an
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of th
e
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison