March 9, 2007 at 3:04 am
Hi Guys,
I am currently developing a DTS package that will display all the running jobs on a csv file.
My stored procedure looks like this:
create proc sp__MonitorRunningJobs
As
DECLARE @count1 int
DECLARE @count2 int
DECLARE @ServerName varchar(300)
DECLARE @Query1 nvarchar(2000)
DECLARE @Query2 nvarchar(2000)
DECLARE @Query3 nvarchar(2000)
-- Truncate the table
--truncate table RunningJobs
set @count1 =0
set @count2 =(Select max(ServerID) from dbo.Server_Lookup where Status = 'Active')
While @count1 <= @count2
begin
set @count1=@count1 +1
set @ServerName =(Select distinct ServerName from dbo.Server_Lookup where Status = 'Active' And Environment Like '%Production%' AND serverID = +@count1)
set @Query2 = 'insert into RunningJobs '
set @Query3 = @Query2+' SELECT * FROM OPENQUERY ('+@ServerName +','+''' exec master.dbo.sp__RunningJobs'')'
--print @ServerName
exec (@Query3)
--print @Query3
end
I created a table callled running jobs that look like this:
create table RunningJobs
(
job_id nvarchar(400),
originating_server nvarchar(4000),
[name] nvarchar(400),
enabled nvarchar(20),
[description] nvarchar(4000),
start_step_id nvarchar(40),
category nvarchar(400),
owner nvarchar(400),
notify_level_eventlog nvarchar(40),
notify_level_email nvarchar(40),
notify_level_netsend nvarchar(40),
notify_level_page nvarchar(40),
notify_email_operator nvarchar(40),
notify_netsend_operator nvarchar(40),
notify_page_operator nvarchar(40),
delete_level nvarchar(40),
date_created datetime,
date_modified datetime,
version_number nvarchar(40),
last_run_date datetime,
last_run_time datetime,
last_run_outcome nvarchar(40),
next_run_time datetime,
next_run_schedule_id nvarchar(40),
current_execution_status nvarchar(40),
current_execution_step nvarchar(40),
current_retry_attempt nvarchar(40),
has_step nvarchar(40),
has_schedule nvarchar(40),
has_target nvarchar(40),
type nvarchar(40)
)
The stored proc being executed within the above stored proc look like this:
create proc sp__RunningJobs
As
exec msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL
When i executed the whole stored proc, I get the following error:
Invalid object name '#xp_results'.
Can anyone pls assist me with this problem.
Kind Regards,
IC
March 11, 2007 at 1:24 pm
'#xp_results' table created in msdb.dbo.sp_get_composite_job_info procedure and temp tables are only session specific...
I think temp is getting dropped before reading it...
You may need to write your own procedure instead of using msdb.dbo.sp_get_composite_job_info with global temp table which ## table...
MohammedU
Microsoft SQL Server MVP
March 13, 2007 at 2:34 am
Hi
I took the code from the msdb and just took some of the code out. and created new tables and do not drop them at the end of the script. The script looks something like this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp__RunningJob
@job_id UNIQUEIDENTIFIER = NULL,
@job_type VARCHAR(12) = NULL, -- LOCAL or MULTI-SERVER
@owner_login_name sysname = NULL,
@subsystem NVARCHAR(40) = NULL,
@category_id INT = NULL,
@enabled TINYINT = NULL,
@execution_status INT = NULL, -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
@date_comparator CHAR(1) = NULL, -- >, < or =
@date_created DATETIME = NULL,
@date_last_modified DATETIME = NULL,
@description NVARCHAR(512) = NULL -- We do a LIKE on this so it can include wildcards
AS
BEGIN
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
SET NOCOUNT ON
-- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
-- This proc should only ever be called by sp_help_job, so we don't verify the
-- parameters (sp_help_job has already done this).
-- Step 1: Create intermediate work tables
Truncate TABLE job_execution_state
Truncate TABLE filtered_jobs
-- Truncate TABLE job_run_details
-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO job_run_details
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
ELSE
INSERT INTO job_run_details
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
INSERT INTO job_execution_state
SELECT xpr.job_id,
xpr.last_run_date,
xpr.last_run_time,
xpr.job_state,
sjs.step_id,
sjs.step_name,
xpr.current_retry_attempt,
xpr.next_run_date,
xpr.next_run_time,
xpr.next_run_schedule_id
FROM job_run_details xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs_view sjv
WHERE (sjv.job_id = xpr.job_id)
-- Step 3: Filter on everything but dates and job_type
IF ((@subsystem IS NULL) AND
(@owner_login_name IS NULL) AND
(@enabled IS NULL) AND
(@category_id IS NULL) AND
(@execution_status IS NULL) AND
(@description IS NULL) AND
(@job_id IS NULL))
BEGIN
-- Optimize for the frequently used case...
INSERT INTO filtered_jobs
SELECT sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs_view sjv
LEFT OUTER JOIN job_execution_state jes ON (sjv.job_id = jes.job_id)
END
ELSE
BEGIN
INSERT INTO filtered_jobs
SELECT DISTINCT
sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in job_execution_state (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id, 0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in job_execution_state
ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in job_execution_state
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in job_execution_state
jes.next_run_time, -- Will be NULL if the job is non-local or is not in job_execution_state
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in job_execution_state
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs_view sjv
LEFT OUTER JOIN job_execution_state jes ON (sjv.job_id = jes.job_id)
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)
WHERE ((@subsystem IS NULL) OR (sjs.subsystem = @subsystem))
AND ((@owner_login_name IS NULL) OR (sjv.owner_sid = SUSER_SID(@owner_login_name)))
AND ((@enabled IS NULL) OR (sjv.enabled = @enabled))
AND ((@category_id IS NULL) OR (sjv.category_id = @category_id))
AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))
OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))
AND ((@description IS NULL) OR (sjv.description LIKE @description))
AND ((@job_id IS NULL) OR (sjv.job_id = @job_id))
END
-- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
UPDATE filtered_jobs
SET current_execution_status = NULL
WHERE (current_execution_status = 4)
AND (job_id IN (SELECT job_id
FROM msdb.dbo.sysjobservers
WHERE (server_id <> 0)))
-- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
-- If we don't have any then the query should return no rows.
IF (@execution_status = 4) AND
(NOT EXISTS (SELECT *
FROM filtered_jobs
WHERE (current_execution_status = 4)))
BEGIN
TRUNCATE TABLE filtered_jobs
END
-- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
-- multi-server jobs there are multiple last run details in sysjobservers, so
-- we simply choose the most recent].
IF (EXISTS (SELECT *
FROM msdb.dbo.systargetservers))
BEGIN
UPDATE filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
(SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
FROM msdb.dbo.sysjobservers
WHERE (job_id = sjs.job_id))
AND (fj.job_id = sjs.job_id)
END
ELSE
BEGIN
UPDATE filtered_jobs
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
END
-- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
-- NOTE: If the job has no jobservers then it wil have a type of 0 meaning
-- unknown. This is marginally inconsistent with the behaviour of
-- defaulting the category of a new job to [Uncategorized (Local)], but
-- prevents incompletely defined jobs from erroneously showing up as valid
-- local jobs.
UPDATE filtered_jobs
SET type = 1 -- LOCAL
FROM filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id = 0)
UPDATE filtered_jobs
SET type = 2 -- MULTI-SERVER
FROM filtered_jobs fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id <> 0)
-- Step 4: Filter on job_type
IF (@job_type IS NOT NULL)
BEGIN
IF (UPPER(@job_type) = 'LOCAL')
DELETE FROM filtered_jobs
WHERE (type <> 1) -- IE. Delete all the non-local jobs
IF (UPPER(@job_type) = 'MULTI-SERVER')
DELETE FROM filtered_jobs
WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs
END
-- Step 5: Filter on dates
IF (@date_comparator IS NOT NULL)
BEGIN
IF (@date_created IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM filtered_jobs WHERE (date_created <> @date_created)
IF (@date_comparator = '>')
DELETE FROM filtered_jobs WHERE (date_created <= @date_created)
IF (@date_comparator = '<')
DELETE FROM filtered_jobs WHERE (date_created >= @date_created)
END
IF (@date_last_modified IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM filtered_jobs WHERE (date_last_modified <> @date_last_modified)
IF (@date_comparator = '>')
DELETE FROM filtered_jobs WHERE (date_last_modified <= @date_last_modified)
IF (@date_comparator = '<')
DELETE FROM filtered_jobs WHERE (date_last_modified >= @date_last_modified)
END
END
-- Return the result set (NOTE: No filtering occurs here)
SELECT sjv.job_id,
sjv.originating_server,
sjv.name,
sjv.enabled,
sjv.start_step_id,
sjv.version_number,
fj.last_run_date,
fj.last_run_time,
fj.last_run_outcome,
next_run_date = ISNULL(fj.next_run_date, 0), -- This column will be NULL if the job is non-local
next_run_time = ISNULL(fj.next_run_time, 0), -- This column will be NULL if the job is non-local
next_run_schedule_id = ISNULL(fj.next_run_schedule_id, 0), -- This column will be NULL if the job is non-local
current_execution_status = ISNULL(fj.current_execution_status, 0), -- This column will be NULL if the job is non-local
current_execution_step = ISNULL(fj.current_execution_step, N'0 ' + FORMATMESSAGE(14205)), -- This column will be NULL if the job is non-local
current_retry_attempt = ISNULL(fj.current_retry_attempt, 0), -- This column will be NULL if the job is non-local
has_step = (SELECT COUNT(*)
FROM msdb.dbo.sysjobsteps sjst
WHERE (sjst.job_id = sjv.job_id)),
has_schedule = (SELECT COUNT(*)
FROM msdb.dbo.sysjobschedules sjsch
WHERE (sjsch.job_id = sjv.job_id)),
has_target = (SELECT COUNT(*)
FROM msdb.dbo.sysjobservers sjs
WHERE (sjs.job_id = sjv.job_id))
FROM filtered_jobs fj
LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc ON (sjv.category_id = sc.category_id)
ORDER BY sjv.job_id
END
I included this script in this script below so that it can go thru each server and get the necessary information:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc sp__MonitorRunningJobs
As
DECLARE @count1 int
DECLARE @count2 int
DECLARE @ServerName varchar(300)
DECLARE @Query1 nvarchar(2000)
DECLARE @Query2 nvarchar(2000)
DECLARE @Query3 nvarchar(2000)
-- Truncate the table
--truncate table RunningJobs
set @count1 =0
set @count2 =(Select max(ServerID) from dbo.Server_Lookup where Status = 'Active')
While @count1 <= @count2
begin
set @count1=@count1 +1
set @ServerName =(Select distinct ServerName from dbo.Server_Lookup where Status = 'Active' And Environment Like '%Production%' AND serverID = +@count1)
set @Query2 = 'insert into RunningJob '
set @Query3 = @Query2+' SELECT * FROM OPENQUERY ('+@ServerName +','+''' exec master.dbo.sp__MonitorJobs'')'
--print @ServerName
exec (@Query3)
--print @Query3
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
However I am still getting the same error:
Invalid object name 'job_run_details'.
can anyone pls help?
IC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply