January 8, 2009 at 7:58 pm
Hi All,
I have a stored procedure which imports data from other database to my local database. What I need to do is to schedule this stored procedure such that it runs at 00:00:00 AM in the morning. If it fails to run at this particular time, it should run at 01:00:00 AM in the morning.
If it started at 00:00:00 AM in the morning successfully, then, no need for it to run at 01:00:00 AM. Can you please tell me how to implement this?
Thanks.
January 9, 2009 at 12:16 am
Definitely this can be achieved, You need have 2 new objects:
1. Metadata table in your database - to track the execution of your Import SP.
2. A SQL Agent job - to execute the Import SP at 00:00 Hrs.
Here are the steps you need to do:
1. Create table TrackImport with 2 columns - DateOfExecution (datetime).
2. Create a SQL Agent job with following steps:
SELECT * FROM TrackImport
WHERE DateOfExecution = convert(datetime,convert ( varchar(8), getdate(), 101 ))
IF @@rowcount > 0
BEGIN
EXEC Import SP
INSERT INTO TrackImport convert(datetime,(convert ( varchar(8), getdate(), 101 )))
END
3. Create schedule for the above job to run everyhour.
Reply, in case of any queries.
-Vikas Bindra
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply