DevTrain Startseite Advanced Developers Conference vom 14.-15. Februar 2011  
  
  
SUCHEN:  
ARTIKEL ONLINE: 525   

Kategorien
.NET
Datenbanken
Web
XML

Allgemein
Camp
Foren
Events
Persönliche Einstellungen
Registrieren
Prämien Shop
Kontakt
Impressum
Über DevTrain

Autoren



 

Forum: MySQL | Thema: AW: SQL 2000 Management- SQL Server Agent- Jobs status abfragen | Von:  ( 19.09.2006 07:29)


Ich kann die eine SP anbieten:
------------------------------
Description:
This example lists information for all currently running jobs.
EXECUTE sp_ListJobInformation 0,1

This example lists information for all enabled jobs, which means the jobs eligible for scheduled execution.
EXECUTE sp_ListJobInformation 1,0

Source Code:

USE master
GO
CREATE PROCEDURE dbo.sp_ListJobInformation
@DBUltra bit = 0,
@PCUltra bit = 0,
@DBIntra varchar(8000) = NULL,
@DBExtra varchar(8000) = NULL,
@PCIntra varchar(100) = NULL,
@PCExtra varchar(100) = NULL,
@PCAdmin varchar(100) = NULL
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(400)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)

DECLARE @SPID smallint

CREATE TABLE #DBAH
(job_id uniqueidentifier
,program_name varchar(34)
,login_time datetime
,last_batch datetime
,run_length datetime
,spid smallint
,spud smallint
,dbid smallint)

CREATE TABLE #DBAZ
(job_id uniqueidentifier
,job_name varchar(100)
,step_count int
,last_run_date int
,last_run_time int
,next_run_date int
,next_run_time int
,schedule_id int
,schedule_name varchar(100)
,requested int
,requester_id int
,requester_name varchar(100)
,enabled int
,running int
,step_id int
,step_name varchar(100)
,subsystem varchar(100)
,retry int
,state int)

SET @PCAdmin = ISNULL(@PCAdmin,'SQLAgent%Job%')

INSERT #DBAH
SELECT 0x0
, SUBSTRING(P.program_name,CHARINDEX('0x',P.program_name),34)
, P.login_time
, P.last_batch
, GETDATE() - P.login_time
, P.spid
, P.blocked
, P.dbid
FROM master.dbo.sysprocesses AS P
WHERE P.program_name LIKE @PCAdmin

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

DECLARE Records CURSOR FAST_FORWARD FOR
SELECT spid, program_name
FROM #DBAH

OPEN Records

FETCH NEXT FROM Records INTO @SPID, @Name

WHILE @@FETCH_STATUS = 0 AND @Status = 0

BEGIN

SET @Task = 'UPDATE #DBAH SET job_id = CONVERT(uniqueidentifier,' + @Name + ') WHERE spid = ' + CONVERT(varchar

(5),@SPID)

EXECUTE (@Task)

FETCH NEXT FROM Records INTO @SPID, @Name

END

CLOSE Records DEALLOCATE Records

INSERT #DBAZ
(job_id
,last_run_date
,last_run_time
,next_run_date
,next_run_time
,schedule_id
,requested
,requester_id
,requester_name
,running
,step_id
,retry
,state)
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,sa

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

UPDATE #DBAZ SET
enabled = O.enabled
, job_name = O.name
, step_name = S.step_name
, subsystem = S.subsystem
, schedule_name = W.name
FROM #DBAZ AS T
JOIN msdb.dbo.sysjobs AS O
ON T.job_id = O.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS S
ON T.job_id = S.job_id AND T.step_id = S.step_id
LEFT JOIN msdb.dbo.sysjobschedules AS W
ON T.job_id = W.job_id AND T.schedule_id = W.schedule_id
WHERE 0 = 0
AND (@DBIntra IS NULL OR CHARINDEX('|'+O.name+'|','|'+(@DBIntra)+'|') > 0)
AND (@DBExtra IS NULL OR CHARINDEX('|'+O.name+'|','|'+(@DBExtra)+'|') = 0)
AND (@PCIntra IS NULL OR O.name LIKE @PCIntra)
AND (@PCExtra IS NULL OR O.name NOT LIKE @PCExtra)

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

UPDATE #DBAZ SET
step_count = I.step_count
FROM #DBAZ AS T
JOIN
(SELECT O.job_id
, COUNT(*) AS step_count
FROM msdb.dbo.sysjobs AS O
JOIN msdb.dbo.sysjobsteps AS S
ON O.job_id = S.job_id
GROUP BY O.job_id) AS I
ON T.job_id = I.job_id

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

DELETE #DBAZ WHERE job_name IS NULL OR (@DBUltra <> 0 AND enabled = 0) OR (@PCUltra <> 0 AND running = 0)

SELECT I.job_id
, I.job_name
, I.step_count
, SUBSTRING(I.last_run_date,1,4) + '.' + SUBSTRING(I.last_run_date,5,2) + '.' + SUBSTRING

(I.last_run_date,7,2) AS last_run_date
, SUBSTRING(I.last_run_time,1,2) + ':' + SUBSTRING(I.last_run_time,3,2) + ':' + SUBSTRING

(I.last_run_time,5,2) AS last_run_time
, SUBSTRING(I.next_run_date,1,4) + '.' + SUBSTRING(I.next_run_date,5,2) + '.' + SUBSTRING

(I.next_run_date,7,2) AS next_run_date
, SUBSTRING(I.next_run_time,1,2) + ':' + SUBSTRING(I.next_run_time,3,2) + ':' + SUBSTRING

(I.next_run_time,5,2) AS next_run_time
, I.schedule_id
, I.schedule_name
, I.enabled
, I.running
, I.retry
, I.state
-- , I.requested
, I.requester_id
, I.requester_name
, I.step_id
, I.step_name
, I.subsystem
, ISNULL(O.name,SPACE(0)) AS database_name
, ISNULL(CONVERT(varchar(20),T.login_time,102),SPACE(0)) AS login_date
, ISNULL(CONVERT(varchar(20),T.login_time, 8),SPACE(0)) AS login_time
, ISNULL(CONVERT(varchar(20),T.last_batch,102),SPACE(0)) AS batch_date
, ISNULL(CONVERT(varchar(20),T.last_batch, 8),SPACE(0)) AS batch_time
, ISNULL(CONVERT(varchar(20),T.run_length, 8),SPACE(0)) AS run_length
, ISNULL(T.spid,0) AS job_spid
, ISNULL(T.spud,0) AS blocking
FROM
(SELECT job_id
, job_name
, step_count
, RIGHT(STR(last_run_date+100000000,9),8) AS last_run_date
, RIGHT(STR(last_run_time+1000000 ,7),6) AS last_run_time
, RIGHT(STR(next_run_date+100000000,9),8) AS next_run_date
, RIGHT(STR(next_run_time+1000000 ,7),6) AS next_run_time
, ISNULL(schedule_id , 0 ) AS schedule_id
, ISNULL(schedule_name,SPACE(0)) AS schedule_name
, enabled
, running
, retry
, state
-- , ISNULL(requested , 0 ) AS requested
, ISNULL(requester_id , 0 ) AS requester_id
, ISNULL(requester_name,SPACE(0)) AS requester_name
, ISNULL(step_id , 0 ) AS step_id
, ISNULL(step_name,SPACE(0)) AS step_name
, ISNULL(subsystem,SPACE(0)) AS subsystem
FROM #DBAZ) AS I
LEFT JOIN #DBAH AS T
ON I.job_id = T.job_id
LEFT JOIN master.dbo.sysdatabases AS O
ON T.dbid = O.dbid

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

DROP TABLE #DBAH

DROP TABLE #DBAZ

SET NOCOUNT OFF

RETURN (@Status)

GO

Günter



Antworten
Vorsicht bei der Eingabe: Die Zeichen ' oder -- sind nicht erlaubt!

 Betreff:
 Nachricht: Den Beitrag finden Sie nun unter: http://beta.devtrain.de/foren Die Benutzerdaten und Foreninhalte von beta.devtrain.de und www.devtrain.de sind die selben.
Sie können sich dort sogar per RSS über neue Inhalte informieren lassen.
Bei Problemen bitte direkt Mail an asp [AT] ppedv.de.

 Signatur:

  



Login
Username:


Passwort:






Passwort vergessen?

Building und Connecting Know-how

© Copyright 2003 ppedv AG