Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Execute SQL job based on trigger file Expand / Collapse
Author
Message
Posted Friday, May 8, 2009 8:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
Guys I need your help to figure out what I am doing wrong here. I need to execute a job based on a trigger file. If the trigger file is not present, the job needs to be executed after 1 hour and check it every hour till it finds the trigger file. I have the script below. This job starts at 4 O clock in the morning and checks for the A_Complete.TXT in \\100-003-p-005\cdf. The problem is it only executes once wheter it finds the tigger file or not. What am I doing wrong?




Check_File_Loop:
truncate table checkfile
insert into checkfile exec master.dbo.xp_fileexist '\\100-003-p-005\cdf\A_complete.TXT'

if not exists (select FileExist from checkfile where FileExist = 1)
BEGIN
WAITFOR DELAY '01:00:00' -- 1 hour, set this for the check interval
GOTO Check_File_Loop
END

BEGIN

exec [msdb].dbo.sp_start_job @job_name='JB_027_E_Extract_Daily'

END



Post #712960
Posted Friday, May 8, 2009 10:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Why not just schedule a job to run hourly and if the file exists process it.

Then the code in one job is:

If file exists THen
run job to process file
Else
exit
End If




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #713106
Posted Friday, May 8, 2009 11:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:54 AM
Points: 241, Visits: 442
Jack Corbett (5/8/2009)
Why not just schedule a job to run hourly and if the file exists process it.

Then the code in one job is:

If file exists THen
run job to process file
Else
exit
End If


The idea makes sense to me too


--------------------------

I long for a job where my databases dont have any pesky users accessing them :)
Post #713178
Posted Friday, May 8, 2009 1:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
But I wanted to run this job only once if file exists. If I schedule it hourly, then the job will run every hour (as long as file is there).
Post #713314
Posted Friday, May 8, 2009 1:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Wouldn't part of processing the file be moving/renaming/deleting it?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #713326
Posted Friday, May 8, 2009 2:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
The file we are taking about here is just the trigger file. This file will not be moved or renamed or deleted. Basically this file will tell me datawarehouse load is complete. So, once trigger file is present, I need to execute my job only once. Am I making sense?
Post #713346
Posted Friday, May 8, 2009 2:29 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:54 AM
Points: 241, Visits: 442
barunpathak (5/8/2009)
The file we are taking about here is just the trigger file. This file will not be moved or renamed or deleted. Basically this file will tell me datawarehouse load is complete. So, once trigger file is present, I need to execute my job only once. Am I making sense?


trigger files are evil.. mmmkay?

that aside.. even if you dont need to do anything with it.. if you DON'T do something with it.. then you'll only ever be able to run the job once..EVER.. because you only want to kick this job off when the file exists. If this is just a once in a lifetime thing.. i wouldn't go to all this work.. just manually kick it off?

best practice here would be to move the file into an archive folder if you absolutely must keep it.. but if its just a trigger.. delete the dang thing.. you dont need it?


--------------------------

I long for a job where my databases dont have any pesky users accessing them :)
Post #713350
Posted Friday, May 8, 2009 3:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
Good suggestion but trigger file exists in the server that is maintained by our parent company so I am not allowed to touch this file.

BTW, this job needs to run T-Sat only once in the event the trigger file exists.
Post #713378
Posted Friday, May 8, 2009 3:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:54 AM
Points: 241, Visits: 442
then id insert an entry in somewhere and then say if you see this entry, then dont do anything.

real pain though.. does the parent company remove the trigger file??


--------------------------

I long for a job where my databases dont have any pesky users accessing them :)
Post #713380
Posted Friday, May 8, 2009 3:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300
No, they don't remove the tirgger file. Its get overwritten every night after the datawarehouse completes.
Post #713393
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse