Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to retrieve Job status for running jobs ?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/24/2004 :  06:33:20  Show Profile  Reply with Quote
Hi,

I am trying to get a list of jobs that are running.
And if possible to get the time at which they were started !?

At the moment I am coercing the list of running jobs from:
xp_sqlagent_enum_jobs

I am not completely satisfied with this situation..
sysjobhistory does not have entries for currently running jobs :-(

/rockmoose

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  06:39:55  Show Profile  Visit mr_mist's Homepage  Reply with Quote
msdb..sp_help_job

-------
Moo. :)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/24/2004 :  07:20:27  Show Profile  Reply with Quote
exec msdb..sp_help_job @execution_status = 1

But is there a way to do this in T-SQL ?

Thanx,
Why dig a canal when You need a trench :-)

/rockmoose
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  07:34:04  Show Profile  Visit mr_mist's Homepage  Reply with Quote
You could always grab the sql code out of the procedure...

-------
Moo. :)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/24/2004 :  08:24:58  Show Profile  Reply with Quote
Yes, and guess what...
xp_sqlagent_enum_jobs ;-)

/rockmoose
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  08:32:22  Show Profile  Visit mr_mist's Homepage  Reply with Quote
I'm working on something similar at the moment, so I may have more later.

-------
Moo. :)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/24/2004 :  08:41:34  Show Profile  Reply with Quote
Ok, nice, appreciate that.

We are putting together a report for our users about the status of all the job runs.

this is what I have at the moment..
( our longest running job so far is a logging job that ran for 1010 hours.. )
( and I have to use sp_help_job to get currently running ones.. will post that <unfinished>part of the script/> as well if you want )


create table #jobruns(
	job_id uniqueidentifier not null,
	originating_server nvarchar(30) not null,
	name sysname not null,
	startjob datetime not null,
	endjob as
		( dateadd(hour,cast(left(runduration_hhhhmmss,4) as int),
			dateadd(minute,cast(substring(runduration_hhhhmmss,5,2) as int),
				dateadd(second,cast(right(runduration_hhhhmmss,2) as int),startjob) ) ) ),
	runduration_hhhhmmss char(8),
	run_status int not null,
	status as(
		case sjh.run_status
			when 0 then 'Failed'
			when 1 then 'Succeeded'
			when 2 then 'Retry'
			when 3 then 'Canceled'
			when 4 then 'In progress'
			else ltrim(sjh.run_status) end ),
	constraint pk_#jobruns_ primary key ( job_id, startjob ),
	constraint uc_#jobruns_ unique clustered( startjob, originating_server, name ) )

insert #jobruns(
	job_id,
	originating_server,
	name,
	startjob,
	runduration_hhhhmmss,
	run_status )
select
	sj.job_id,
	sj.originating_server,
	sj.name,
	startjob = cast(ltrim(sjh.run_date)
	+' '+left(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),2)
	+':'+substring(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),3,2)
	+':'+right(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),2) as datetime),
	runduration_hhhhmmss = 
	left(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),4)
	+substring(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),5,2)
	+right(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),2),
	sjh.run_status
from
	msdb.dbo.sysjobs sj
	join msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
where
	sjh.step_id = 0

select * from #jobruns

drop table #jobruns
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  09:31:37  Show Profile  Visit mr_mist's Homepage  Reply with Quote
That's looking familiar. Mine does a slightly different thing, in that it was firstly designed to list failing jobs across a number of servers (server names are maintained in a table.) I'm now trying to expand it to cover jobs that are still running, hence hit the same problem..


/*

CREATE TABLE [dbo].[monitor_jobs_on_these_servers] (
	[servername] [sysname] NOT NULL ,
	[counter] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[monitor_jobs_on_these_servers] WITH NOCHECK ADD 
	CONSTRAINT [pk_monitor_jobs_on_these_servers] PRIMARY KEY  CLUSTERED 
	(
		[counter]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [IX_monitor_jobs_on_these_servers] ON [dbo].[monitor_jobs_on_these_servers]([servername]) ON [PRIMARY]
GO


create table jobmon_history
(
job_name sysname,
run_status varchar(15),
message nvarchar(1024),
run_date datetime,
run_duration smallint,
server sysname
)

create unique clustered index clx1 on jobmon_history (server, job_name, run_date ) with ignore_dup_key
*/

alter PROC jobmon
@servername nvarchar(30) = NULL,
@history bit = 0
AS
set nocount on 
DECLARE @counter smallint

/* Call this with a servername to see jobs for one server, or with no parameter to
see all the servers in the table monitor_jobs_on_these_servers. The proc also
updates the jobmon_history table with any new job failures. 

If called with @history = 1 the proc will simply show the existing history table, without checking the latest
job situation. You can also view the history for just one server.

*/

--declare @servername nvarchar (30)
--SELECT @servername =  servername from monitor_jobs_on_these_servers

--select @servername

If @history = 1 begin

SELECT * from jobmon_history
WHERE @servername is null or @servername = server

END ELSE BEGIN

	IF @servername is null BEGIN
	--print 'hello'
	select @counter = 0
	WHILE @counter < (SELECT max (counter) from monitor_jobs_on_these_servers)
	BEGIN
	SELECT TOP 1 @counter =  counter, @servername = servername from monitor_jobs_on_these_servers where counter > @counter
	--print @counter
	--print @servername
	exec jobmon @servername
	END
	
	END ELSE BEGIN
	
	declare @sql nvarchar (1400)
	
	select @sql =
	'SELECT  
		job_name = sj.name,
	        case sjh.run_status 
			when 0 then ''Failed ''
			when 1 then ''Succeeded ''
			when 2 then ''Retry (step only) ''
			when 3 then ''Canceled ''
			when 4 then ''In-progress message ''
			when 5 then ''Unknown ''
		end as [run status ],
		message,
		convert (datetime, convert(varchar(9),run_date),112) + convert (datetime,left ((convert (varchar(9), run_time)),2)+'':''+right(left ((convert (varchar(9), run_time)),4),2)+'':''+right(((convert (varchar(9), run_time))),2),8) as run_date,
	        sjh.run_duration,
	        sjh.server
	FROM 
		[' + @servername + '].msdb.dbo.sysjobhistory                sjh 
	INNER JOIN 
		[' + @servername + '].msdb.dbo.sysjobs_view                 sj
		on sjh.job_id = sj.job_id
	where
	run_status in (0,3,4,5) -- Failed, cancelled, in prog, unknown
	and
	step_id > 0 -- this seems to give more useful info
	
	group by name, run_status, message, run_date, run_duration, server, run_time
	order by run_date
	'
	
	exec sp_executesql  @statement = @sql
	
	select @sql = 'INSERT INTO jobmon_history ' + @sql
	
	exec sp_executesql  @statement = @sql
	END
END




-------
Moo. :)
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  09:32:48  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Of course, I was expecting "in progress" ones to show up too. But they don't, as, as you say, they don't appear in the history list. :|

-------
Moo. :)
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  10:41:36  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Okay, master.dbo.xp_sqlagent_enum_jobs 1,'sa' will get me the elusive "running" column, but where do I get the time that the run started from? I'm sure the value in "last run time" for the sysjobsteps view is the time that the last run ended, not the time that the current run started. :|

-------
Moo. :)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/24/2004 :  11:05:20  Show Profile  Reply with Quote
Hi,
Yes master.dbo.xp_sqlagent_enum_jobs 1,'' is also how far i got.
Still haven't figured out how to get the starting time for those running jobs...
I thought about querying the sysjobschedules for minute, but this seems a drag :-(, and the job could be manually started !.
SQL-DMO : JobObject.LastRunTime Property... ?!?!
BOL - "The LastRunTime property identifies the most recent time at which SQLServerAgent attempted execution of the referenced job or job step"

Also EM just shows the last run_time for (succesful,canceled,failed) jobs, not the time a running job was started.
elusive..

/rockmoose
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/24/2004 :  11:09:35  Show Profile  Visit mr_mist's Homepage  Reply with Quote
I was thinking maybe last batch from sysprocesses, but it's quite hacky.

Otherwise I am just left with a misleading column. How pesky.

Don't know about DMO, I'm trying to do this all from t-sql to keep it in a procedure.

-------
Moo. :)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/24/2004 :  11:33:48  Show Profile  Reply with Quote
Yeah,

I was beginning to consider.. select isnull(convert(varchar,run_date,120),'still running since ?') ...
( at least that is "correct" info for the user ).

DMO, it is possible to get it into a proc with sp_OACreate etc..
But I will not go down that path this time.
sysprocesses interesting hack :-)

/rockmoose
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/25/2004 :  06:28:44  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Unfortunately my sysprocesses idea fell flat, the jobid is not what I was expecting.

I think I may have to produce a NULL for the run field for those entries that are still running. Since it should be unlikely to have any RUNNING jobs during the day, this can be enough of a cause for further manual investigation.

I'm surprised noone else has given any more ideas towards this. I'm sure it must be a common want.

-------
Moo. :)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/25/2004 :  07:18:02  Show Profile  Reply with Quote
Yes, all this was very annoying and I end up with null as well for running jobs.
It is not a major problem, and time to move on.

Anyway here is the code I used for reading xp_sqlagent_enum_jobs, to identify the currently running ones.
( I used this instead of sp_help_job because that procs resultset is kindof w i d e . )
/* this is pretty much ripped out of the ms procs */


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 )

insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''

select job_id as currently_running_jobs
from #xp_results
where running = 1

drop table #xp_results


/rockmoose
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 08/25/2004 :  07:44:12  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Yep. I think I've used the exact same stuff. Here's how I wrapped it all up...



--USAGE : jobmon 'servername' - Running jobs & cancelled/failed jobs for one server
--jobmon -- Running jobs & cancelled/failed jobs for all servers named in supporting table
--jobmon @history = 1 - Shows old cancelled jobs from the history.
--jobmon 'servername',@history = 1 - shows history for one server.


/*

CREATE TABLE [dbo].[monitor_jobs_on_these_servers] (
	[servername] [sysname] NOT NULL ,
	[counter] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]


ALTER TABLE [dbo].[monitor_jobs_on_these_servers] WITH NOCHECK ADD 
	CONSTRAINT [pk_monitor_jobs_on_these_servers] PRIMARY KEY  CLUSTERED 
	(
		[counter]
	)  ON [PRIMARY] 


 CREATE  UNIQUE  INDEX [IX_monitor_jobs_on_these_servers] ON [dbo].[monitor_jobs_on_these_servers]([servername]) ON [PRIMARY]



create table jobmon_history
(
job_name sysname,
run_status varchar(15),
message nvarchar(1024),
run_date datetime,
run_duration smallint,
server sysname
)

create unique clustered index clx1 on jobmon_history (server, job_name, run_date ) with ignore_dup_key
*/

alter PROC jobmon
@servername nvarchar(30) = NULL,
@history bit = 0
AS
set nocount on 
DECLARE @counter smallint

/* 

This procedure will show you failed jobs since the last time that the procedure
was ran, and any currently running jobs (without date/time.)

Call this with a servername to see failed jobs for one server, or with no parameter to
see all the servers in the table monitor_jobs_on_these_servers. The proc also
updates the jobmon_history table with any failures. 

If called with @history = 1 the proc will simply show the existing history table, without checking the latest
job situation. You can also view the history for just one server.

*/

--declare @servername nvarchar (30)
--SELECT @servername =  servername from monitor_jobs_on_these_servers

--select @servername

If @history = 1 begin

SELECT * from jobmon_history
WHERE @servername is null or @servername = server
order by server, job_name, run_date

END ELSE BEGIN

	IF @servername is null BEGIN
	--print 'hello'
	select @counter = 0
	WHILE @counter < (SELECT max (counter) from monitor_jobs_on_these_servers)
	BEGIN
	SELECT TOP 1 @counter =  counter, @servername = servername from monitor_jobs_on_these_servers where counter > @counter
	--print @counter
	--print @servername
	exec jobmon @servername
	END
	
	END ELSE BEGIN
	
	declare @sql nvarchar (3500)
	declare @selonly nvarchar (2200)
	
	select @sql =
	'SELECT  
		job_name = sj.name,
	        case sjh.run_status 
			when 0 then ''Failed ''
			when 1 then ''Succeeded ''
			when 2 then ''Retry (step only) ''
			when 3 then ''Canceled ''
			when 4 then ''In-progress message ''
			when 5 then ''Unknown ''
		end as [run status ],
		sjh.message,
		convert (datetime, convert(varchar(9),sjh.run_date),112) + convert (datetime,left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),2)+'':''+right(left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), sjh.run_time),6))),2),8) as run_date,
	        sjh.run_duration,
	        sjh.server
	FROM 
		[' + @servername + '].msdb.dbo.sysjobhistory                sjh 
	INNER JOIN 
		[' + @servername + '].msdb.dbo.sysjobs_view                 sj
		on sjh.job_id = sj.job_id
	LEFT OUTER JOIN 
		jobmon_history jh
		on jh.job_name = sj.name AND jh.server = sjh.server and jh.run_date = convert (datetime, convert(varchar(9),sjh.run_date),112) + convert (datetime,left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),2)+'':''+right(left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), sjh.run_time),6))),2),8)
		and 
		(
		sjh.run_status = 0 and jh.[run_status] =  ''Failed '' OR
		sjh.run_status = 1 and jh.[run_status] =  ''Succeeded '' OR
		sjh.run_status = 2 and jh.[run_status] =  ''Retry (step only) '' OR
		sjh.run_status = 3 and jh.[run_status] =  ''Canceled '' OR
		sjh.run_status = 4 and jh.[run_status] =  ''In-progress message '' OR
		sjh.run_status = 5 and jh.[run_status] =  ''Unknown ''
		)
	where
	sjh.run_status in (0,3,4,5) -- Failed, cancelled, in prog, unknown
	and
	step_id > 0 -- this seems to give more useful info
	and
	jh.job_name is null
	
	group by name, sjh.run_status, sjh.message, sjh.run_date, sjh.run_duration, sjh.server, run_time
	order by sjh.server,sjh.run_date
	'
	
	select @selonly = @sql

	select @sql = 
	'
	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)

	INSERT into #xp_results exec (''['+ @servername + '].master.dbo.xp_sqlagent_enum_jobs 1,'''''''''')


	select s.name, ''Running'' as run_status, ''Executing Job Step '' + convert(varchar(4),step_id) + '' : '' + step_name as ''Message'',
	Null as run_date,
	NULL as ''run_duration'', originating_server as server
	from #xp_results x 
	inner join msdb..sysjobs s on x.job_id = s.job_id 
	inner join msdb..sysjobsteps ss on ss.job_id = s.job_id and ss.step_id = current_step
	where running = 1
	
	union all
	' + @sql + '
	DROP TABLE #xp_results'

--	print @sql

	exec sp_executesql  @statement = @sql
	
	select @sql = 'INSERT INTO jobmon_history ' + @selonly
	
	exec sp_executesql  @statement = @sql
	END
END



-------
Moo. :)
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/25/2004 :  07:45:34  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
I think it's interesting to see how many of us do similar things. I had to create a proc to disable my transaction log restores, then verify there were no jobs running on the backup server so I could clone the LUNs on the EMC. Here is the script for that.


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE EMCBackupLogRestoresDisable

--Name:		EMCBackupLogRestoresDisable
--
-- (c) 2004 NovaStar Mortgage, Inc.
--
--Purpose: 	This stored Procedure(SP) stops log restores and verifies the restores have stopped.
--
--Format:	EXEC EMCBackupLogRestoresDisable @emc_clone_master_key
--
--Example:	EXEC EMCBackupLogRestoresDisable '1'
--
--Action:	Author:			Date:		Comments:
---------	-------------   --/--/----	----------------------------------------
--Created	Derrick Leggett	06/10/2004 	Initial Development 
--Modified	Derrick Leggett	06/25/2004	Added nocount.
--Modified	Derrick Leggett	07/19/2004	Added error handling.
--

@emc_clone_master_key INT

AS

SET NOCOUNT ON

--Declare needed variables.
DECLARE
	@sql NVARCHAR(2500),
	@emc_clone_details_procedure VARCHAR(256),
	@emc_clone_details_type_key VARCHAR(55),
	@emc_clone_details_info VARCHAR(1000),
	@emc_clone_details_parameters VARCHAR(2000),
	@cmd NVARCHAR(2500),
	@string NVARCHAR(4000),
	@job_disabled_successfully INT,
	@loop INT, --Current iteration of loop.
	@loop_cycle INT, --Variable to record how max times to loop.
	@loop_lag CHAR(8) --Variable to record how long between loops.

SELECT 
	@emc_clone_details_procedure = (SELECT OBJECT_NAME(@@PROCID)),
	@emc_clone_details_info = '',
	@emc_clone_details_parameters = '@emc_clone_master_key = ' + CAST(@emc_clone_master_key AS VARCHAR(55)),
	@loop = 1, 
	@loop_cycle = 240,
	@loop_lag = '00:00:30',
	@job_disabled_successfully = 0

--Record the start of process.
EXEC EMCCloneDetailsPut
	@emc_clone_master_key, 
	@emc_clone_details_procedure, 
	'Started', 
	@emc_clone_details_info,
	@emc_clone_details_parameters

--Drop/Create temp table to hold status.
IF (SELECT OBJECT_ID('tempdb..#status')) IS NOT NULL
	BEGIN
		DROP TABLE #status
	END

CREATE TABLE #status(
	job_id UNIQUEIDENTIFIER,
	originating_server NVARCHAR(256),
	name NVARCHAR(256),
	enabled INT,
	description VARCHAR(2000),
	start_step_id INT,
	category VARCHAR(55),
	owner VARCHAR(55),
	notify_level_eventlog INT,
	notify_level_email INT,
	notify_level_netsend INT,
	notify_level_page INT,
	notify_email_operator VARCHAR(55),
	notify_netsend_operator VARCHAR(55),
	notify_page_operator VARCHAR(55),
	delete_level INT,
	date_created DATETIME,
	date_modified DATETIME,
	version_number INT,
	last_run_date CHAR(8),
	last_run_time VARCHAR(8),
	last_run_outcome INT,
	next_run_date CHAR(8),
	next_run_time VARCHAR(8),
	next_run_schedule_id INT,
	current_execution_status INT,
	current_execution_step VARCHAR(255),
	current_retry_attempt INT,
	has_step INT,
	has_schedule INT,
	has_target INT,
	type INT)

--Turn the restore job off.
EXEC MKCSSQL04.msdb.dbo.sp_update_job @job_name = 'Restore Transaction Logs from Prod', @enabled = '0'

IF @@ERROR <> 0
	BEGIN
		SELECT @emc_clone_details_info = @emc_clone_details_info + 'Failure on sp_update_job.'
		GOTO ERROR_HANDLE
	END

--Verify the job has been disabled.
IF (SELECT enabled FROM MKCSSQL04.msdb.dbo.sysjobs WHERE name = 'Restore Transaction Logs from Prod' AND enabled = '0') IS NULL
	BEGIN
		SELECT @emc_clone_details_info = @emc_clone_details_info + 'Job was not disabled.'
		GOTO ERROR_HANDLE
	END

--Loop through until the restore job shows as being disabled.  
--  When it's been disabled, report success.
--  If it takes more then 30 minutes, report a failure.

WHILE @loop <= @loop_cycle
	BEGIN

		DELETE #status
		INSERT #status
		EXEC MKCSSQL04.msdb.dbo.sp_get_composite_job_info @execution_status = 1
		
		IF @@ERROR <> 0
			BEGIN
				SELECT @emc_clone_details_info = @emc_clone_details_info + 'Failure on sp_get_composite_job_info.'
				GOTO ERROR_HANDLE
			END

		IF (SELECT TOP 1 job_id FROM #status) IS NULL
			BEGIN
				SELECT @job_disabled_successfully = 1
				GOTO END_LOOP
			END

		WAITFOR DELAY @loop_lag
	
	END

END_LOOP:

IF @job_disabled_successfully = 1
	BEGIN

		--Record the success of process.
		EXEC EMCCloneDetailsPut
			@emc_clone_master_key, 
			@emc_clone_details_procedure, 
			'Success', 
			@emc_clone_details_info,
			@emc_clone_details_parameters

		RETURN 0
	END
ELSE
	BEGIN
		SELECT @emc_clone_details_info = @emc_clone_details_info + 'Job disabled, but has not completed.'
		GOTO ERROR_HANDLE

	END

ERROR_HANDLE:

	--Record the failure of process.
	EXEC EMCCloneDetailsPut
		@emc_clone_master_key, 
		@emc_clone_details_procedure, 
		'Failure', 
		@emc_clone_details_info,
		@emc_clone_details_parameters

	--Raiserror and exit batch with failure.
	RAISERROR(@emc_clone_details_info,16,1)
	RETURN -1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

vjimmers
Starting Member

USA
6 Posts

Posted - 05/06/2009 :  15:14:42  Show Profile  Reply with Quote
Thanks you all for the info. I do have one suggestion. I notice some people are coding dynamic server names so that can reference linked servers which isn't practical for a server farm.

I use a wrapper procedure for commands that return data. I use a different one for executing things that do not return a data set.



INSERT INTO #temp(
[job_id]
,[originating_server]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category]
,[owner]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator]
,[notify_netsend_operator]
,[notify_page_operator]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,[last_run_date]
,[last_run_time]
,[last_run_outcome]
,[next_run_date]
,[next_run_time]
,[next_run_schedule_id]
,[current_execution_status]
,[current_execution_step]
,[current_retry_attempt]
,[has_step]
,[has_schedule]
,[has_target]
,[type])
EXEC @err = [dbo].[usp_srvr_exec]
@cmd = 'msdb.dbo.sp_help_job'
, @DisplayError = 1
, @ServerName = @@SERVERNAME
, @Execute0Display1Both2 = 2
, @UseOpenRowSet = 1


When passed the command msdb.dbo.sp_help_job for the current server usp_srvr_exec normally produces a command formated as

EXEC('msdb.dbo.sp_help_job').

If passed the name of a remote server (not linked) it produces a command

SELECT * FROM OPENROWSET('SQLNCLI','ServerName';'DBName';'DBPassword', 'set fmtonly off; EXEC msdb.dbo.sp_help_job')

The DB names and passwords are encrypted in a table and retrieved as needed.

If called with the input parameter @UseOpenRowSet = 1 it will also produce a command formated like

SELECT * FROM OPENROWSET('SQLNCLI','ServerName';'DBName';'DBPassword', 'set fmtonly off; EXEC msdb.dbo.sp_help_job')

This can used to prevent receiving the error message "An INSERT EXEC statement cannot be nested."

Good luck!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000