May 8, 2009 at 8:21 am
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
May 8, 2009 at 10:04 am
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
Consultant - Straight Path Solutions
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
May 8, 2009 at 11:02 am
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 🙂
May 8, 2009 at 1:44 pm
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).
May 8, 2009 at 1:59 pm
Wouldn't part of processing the file be moving/renaming/deleting it?
Jack Corbett
Consultant - Straight Path Solutions
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
May 8, 2009 at 2:24 pm
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?
May 8, 2009 at 2:29 pm
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 🙂
May 8, 2009 at 3:06 pm
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.
May 8, 2009 at 3:13 pm
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 🙂
May 8, 2009 at 3:35 pm
No, they don't remove the tirgger file. Its get overwritten every night after the datawarehouse completes.
May 8, 2009 at 3:39 pm
what you're saying doesn't make sense though..
unless they're storing data in the trigger file with the current date in..
data warehouse job completes
they drop a file
your process runs ONLY if the file is there
you do not remove the trigger file, so the next day, the file is already there no matter what.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
May 9, 2009 at 5:14 pm
Yes That is correct. Trigger file doesn't contain data. It is just an indication that datawarehoue load is complete. This file will be there next day no mater what.
May 10, 2009 at 6:27 pm
Well, I don't think that this is a great way for your parent company to interact with your SQL Server DB. If you have to use this so-called trigger file, then here is how I would do it:
1) Write a stored procedure that use code like this:
Create Table #TriggerFile(fileTxt nvarchar(500))
INSERT into #TriggerFile
exec xp_cmdshell 'DIR {trigger file name}'
to capture the trigger file's DIR info. Then parse the first X characters of that line extract the file date and convert it to a datetime.
2) Compare the datetime to the time stored in a table from the last time the trigger file triggered the Job execution.
3) If the file date is newer than the stored date, then do the Job processing. Otherwise, exit.
4) When done, then update the stored time.
5) Create a job to run the Proc periodically (once an hour is probably good).
Now the Job wiill run once an hour, but will only do the processing if the trigger file's datetime has changed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 11, 2009 at 7:02 am
Thats a nice solution for a horrible situation.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
May 12, 2009 at 3:36 pm
Thanks. I have implemented the solution as you described.
Thanks again!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply