Execute SQL job based on trigger file

  • 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

  • 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 (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 🙂

  • 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).

  • Wouldn't part of processing the file be moving/renaming/deleting it?

  • 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?

  • 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 🙂

  • 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.

  • 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 🙂

  • No, they don't remove the tirgger file. Its get overwritten every night after the datawarehouse completes.

  • 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 🙂

  • 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.

  • 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]

  • Thats a nice solution for a horrible situation.

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • 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