How to reschedule a job or a process

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to reschedule a job or a process

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-04-25 : 14:07:24

Hello All,

I have a job that executes a stored procedure called usp_LOAD_PROCESSES, every morning at 5:00am. The problem I'm having is that since this operation retrieves data from an Oracle database and inserts it into a SQL Server database; some times the data is not always available on the Oracle side. Therefore I was thinking about writing a script to check if the data was available after that particular process had finished running, if the record count comes back zero it should automatically reschedule that particular process to rerun in the next 30mins.

I know how to check if the data comes back zero or not for the record count but I do not know how to reschedule that particular process to run again in the next 30mins. Does anyone know how to reschedule a job or a process for a time period? Please advice.

My sample scripts are shown below. Thanks.

CREATE PROCEDURE [dbo].[usp_LOAD_PROCESSES] (@dtRptDateDetail datetime)

AS
BEGIN

DECLARE @dtRptDateDetailPlusOne datetime
SET @dtRptDateDetailPlusOne=dateadd(d,1,@dtRptDateDetail)

DECLARE @sqlstring Nvarchar(4000)

PRINT 'DELETING FOR INSERT DATE'
DELETE FROM dbo.OFFICE_SUMMARY WHERE rpt_date_detail=@dtRptDateDetail

--PROCESS_1
SET @sqlstring='INSERT INTO TABLE_A SELECT *, '''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''' as rpt_date_detail, ''PROCESS_1'' as process_name FROM OPENQUERY(PROCESS_1, ''SELECT *
FROM Category WHERE S_DATE >= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''') AND S_DATE < TO_DATE('''''+ CONVERT(varchar(12),@dtRptDateDetailplusone,101) + ''''',''''MM/DD/YYYY'''')'')'
exec sp_executesql @sqlstring

-- PROCESS_2
SET @sqlstring='INSERT INTO TABLE_A SELECT *, '''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''' as rpt_date_detail, ''PROCESS_2 '' as process_name FROM OPENQUERY(PROCESS_2, ''SELECT *
FROM Category WHERE S_DATE >= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''') AND S_DATE < TO_DATE('''''+ CONVERT(varchar(12),@dtRptDateDetailplusone,101) + ''''',''''MM/DD/YYYY'''')'')'
exec sp_executesql @sqlstring

-- PROCESS_3
SET @sqlstring='INSERT INTO TABLE_A SELECT *, '''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''' as rpt_date_detail, ''PROCESS_3 '' as process_name FROM OPENQUERY(PROCESS_3, ''SELECT *
FROM Category WHERE S_DATE >= TO_DATE('''''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''') AND S_DATE < TO_DATE('''''+ CONVERT(varchar(12),@dtRptDateDetailplusone,101) + ''''',''''MM/DD/YYYY'''')'')'
exec sp_executesql @sqlstring

--Checking the table to see if it has data or not
SELECT COUNT(*), rpt_date_detail, process_name
FROM TABLE_A
GROUP BY rpt_date_detail, process_name
HAVING (rpt_date_detail between convert(datetime, '2008-04-24 00:00:00', 102) and convert(datetime, '2008-04-25 00:00:00', 102)) and
(process_name = 'PROCESS_1')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 14:12:20

You should be able to modify the schedule of your jobs by going to jobs section under your database in enterprise amanger.

http://msdn2.microsoft.com/en-us/library/aa176984(SQL.80).aspx

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-25 : 14:23:08

I will sometimes make a first step that is written to fail if a certain condition is not met(like the presence of data). Then I set that step to retry X number of times every 15 minutes or so before giving up.

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources