SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute SQL job based on trigger file


Execute SQL job based on trigger file

Author
Message
SqlSavvy
SqlSavvy
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46673 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
torpkev
torpkev
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 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 Smile
SqlSavvy
SqlSavvy
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 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).
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46673 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
SqlSavvy
SqlSavvy
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 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?
torpkev
torpkev
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 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 Smile
SqlSavvy
SqlSavvy
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 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.
torpkev
torpkev
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1091 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 Smile
SqlSavvy
SqlSavvy
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 300
No, they don't remove the tirgger file. Its get overwritten every night after the datawarehouse completes.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search