Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: SP halts on error when run from a scheduled job

    I have a stored procedure that is scheduled as a job. The job is part of a log shipping process. If I run the job from QA, when an error is thrown, the sp continues on. When the job runs that fires off the sp, when an error is thrown, the job halts, and does not continue.

    Any help would be great,
    Thanks
    Tom

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    A job is stops on an error. There is no way you can prevent that except rectify the error
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Sep 2003
    Posts
    32
    Thanks for your reply, but, ahhhh!

    This cannot be! Are you sure there is no way to hide or ignore the error?

    on error resume next!!!

    Tom

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Nope !!! I havent found out a way to do so till now ...

    Gurus .. any Ideas ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Ressurecting the thread...

    Hi all,

    I did a search for my job troubles, and found this thread. I am having a similar problem, which may have the same answer, but thought it worth tossing out (until I can get Try-Catch).

    Anywhoo...I have a job that calls a stored proc that unpdates the run date for a few jobs that are run on a daily basis. As background, this scheduling job runs each day to set up scheduled jobs for the current day. We are doing this to handle holidays, so that the jobs do not run on holidays. The code is
    Code:
    CREATE PROCEDURE [dbo].[VerificationJobScheduler]
    AS
    
    DECLARE @CurrDate varchar(10)
    DECLARE @m_error int
    DECLARE @FailCount int
    DECLARE @LocalMsg varchar(100)
    
    SELECT @CurrDate = CONVERT(varchar(10), GETDATE(), 112)
    SET @FailCount = 0
    
    IF NOT EXISTS (	SELECT * 
    		FROM APRECEIVE1.IBDDailyProd.dbo.sysobjects
    		WHERE [Name] = 'XML_PRINTDATE' 
    			AND Uid = 1 
    			AND Type = 'U')
    BEGIN
    	/*----- Log the error and bail out, screaming indignantly all the way							*/
    	SET @LocalMsg = 'APRECEIVE1.XML_PRINTDATE DOES NOT EXIST, exiting JOB scheduler'
    	PRINT @LocalMsg
    	SET @m_error = 50001
    	GOTO ErrorHandler
    END
    
    /*----- Check the XML_PRINTDATE file to make sure today is not a holiday							*/
    IF (	(SELECT Todays_Holiday 
    	FROM APRECEIVE1.IBDDailyProd.dbo.XML_PRINTDATE 
    	WHERE CONVERT(varchar(10),Todays_Date, 112) = CONVERT(varchar(10), GETDATE(), 112)) = 0)
    BEGIN
    	/*---------------------------------------------------------------------------------------
    	|	On non-holiday days, schedule all the following jobs for the current date
    	|---------------------------------------------------------------------------------------*/
    	/*----- ACC/DIS (AM) Cross-Databases Column Matching					*/
    	EXECUTE @m_error = MSDB.dbo.sp_update_jobschedule  
    		@job_name = 'VerifyDB - ACC/DIS (AM) Match', 
    	   	@name = 'AUTO_SCHEDULE',
    		@freq_type = 1,  -- Once Only
    		@active_start_date =  @CurrDate,
    		@enabled = 1,
    		@active_start_time = 100000
    	IF @m_error <> 0
    	BEGIN
    		SET @FailCount = @FailCount + 1
    		PRINT '--> Unable to schedule verification job: VerifyDB - ACC/DIS (AM) Match for ' + @CurrDate
    	END
    
    	/*----- Climax Top Date Cross-Databases Column Matching					*/
    	EXECUTE @m_error = MSDB.dbo.sp_update_jobschedule  
    		@job_name = 'VerifiyDB - Climax Top Date Match', 
    	   	@name = 'AUTO_SCHEDULE',
    		@freq_type = 1,  -- Once Only
    		@active_start_date =  @CurrDate,
    		@enabled = 1,
    		@active_start_time = 101000
    	IF @m_error <> 0
    	BEGIN
    		SET @FailCount = @FailCount + 1
    		PRINT '--> Unable to schedule verification job: VerifyDB - Climax Top Date Match for ' + @CurrDate
    	END
    
    	/*----- VerifyDB - F_DailyData Values And Ratings Match Cross-Databases Column Matching			*/
    	EXECUTE @m_error = MSDB.dbo.sp_update_jobschedule  
    		@job_name = 'VerifyDB - F_DailyData Values And Ratings Match', 
    	   	@name = 'AUTO_SCHEDULE',
    		@freq_type = 1,  -- Once Only
    		@active_start_date =  @CurrDate,
    		@enabled = 1,
    		@active_start_time = 153000
    	IF @m_error <> 0
    	BEGIN
    		SET @FailCount = @FailCount + 1
    		PRINT '--> Unable to schedule verification job: VerifyDB - F_DailyData Values And Ratings Match for ' + @CurrDate
    	END
    
    	/*----- IBDDailyProd_Top100SymbolRankMatch Cross-Databases Column Matching			*/
    	EXECUTE @m_error = MSDB.dbo.sp_update_jobschedule  
    		@job_name = 'VerifyDB - IBDDailyProd Top100SymbolRankMatchy', 
    	   	@name = 'AUTO_SCHEDULE',
    		@freq_type = 1,  -- Once Only
    		@active_start_date =  @CurrDate,
    		@enabled = 1,
    		@active_start_time = 153000
    	IF @m_error <> 0
    	BEGIN
    		SET @FailCount = @FailCount + 1
    		PRINT '--> Unable to schedule verification job: VerifyDB - IBDDailyProd Top100SymbolRankMatch for ' + @CurrDate
    	END
    
    	/*----- IBDIndex_CloseIndex_Match Cross-Databases Column Matching			*/
    	EXECUTE @m_error = MSDB.dbo.sp_update_jobschedule  
    		@job_name = 'VerifyDB - IBDIndex CloseIndex Matchy', 
    	   	@name = 'AUTO_SCHEDULE',
    		@freq_type = 1,  -- Once Only
    		@active_start_date =  @CurrDate,
    		@enabled = 1,
    		@active_start_time = 153000
    	IF @m_error <> 0
    	BEGIN
    		SET @FailCount = @FailCount + 1
    		PRINT '--> Unable to schedule verification job: VerifyDB - IBDIndex_CloseIndex_Match Match for ' + @CurrDate
    	END
    
    	/*----- ACC/DIS (AM) Cross-Databases Column Matching					*/
    	EXECUTE @m_error = MSDB.dbo.sp_update_jobschedule  
    		@job_name = 'VerifyDB - ACC/DIS (PM) Match', 
    	   	@name = 'AUTO_SCHEDULE',
    		@freq_type = 1,  -- Once Only
    		@active_start_date =  @CurrDate,
    		@enabled = 1,
    		@active_start_time = 160000
    	IF @m_error <> 0
    	BEGIN
    		SET @FailCount = @FailCount + 1
    		PRINT '--> Unable to schedule verification job: VerifyDB - ACC/DIS (PM) Match for ' + @CurrDate
    	END
    
    END
    
    RETURN (@FailCount)
    
    ErrorHandler:
    	DECLARE @func varchar(255)
    	SELECT @func = Object_name(@@Procid)
    	EXECUTE [dbo].[ErrorHandler] @m_error, 'DBVerification sp', @func, @LocalMsg
    	RETURN (@m_error)
    GO
    Perhaps I am trying to be TOO fancy, but would like the job to run and create all jobs it can, but let me know which ones failed.

    Trouble is, when I schedule this job to run each morning, so that it calls THIS proc to update the rest of the day's job schedule, when one fails, the SQL Agent bails out of the job (even though the proc continues on when run from sql analyzer).

    Is there still no way anyone can think of to run this job (besides having the first job execute a .bat file on the server, which osql's the SQL Server scheduling proc (above)? Sheesh...there is really no way to schedule a job and keep internal errors from crashing the agent-level job? :fume:
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •