Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post Unanswered: Sql Server Scheduler -- quesion please

    I have a table called FOO that has 1 column called BooleanRunning

    There is only 1 record in there, and it is either 'YES' or 'NO'.

    I would like SQL Server Scheduler to do the following

    STEP 1:
    Check first record of table Foo. If BooleanRunning='YES' then fail. If BooleanRunning='NO then Step is SUCCESSFUL, GO TO STEP 2

    STEP 2:
    Execute my Vb Program.


    HOW DO I DO STEP 1?

    My VB program sets BooleanRunning to "YES" at the beginning and sets it to "NO" at the end. (I could have the VB program check BooleanRunning ="yes" then quit, but that still means I would have to start a second instance of the vb program which I do not want).

    Can someone help?

    ~Le

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Sql Server Scheduler -- quesion please

    You can issue sp_stop_job to stop the job.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Re: Sql Server Scheduler -- quesion please

    Originally posted by joejcheng
    You can issue sp_stop_job to stop the job.
    The scheduler must attempt to start the job every 20 minutes from 9am to 5pm.

    I could have the 1st step execute a Stored Procedure to check if BooleanRunning = 'Yes', and if it does execute the sp_stop_job

    But will the scheduler know to try and restart the job 10 minutes later?

    ~Le

  4. #4
    Join Date
    Nov 2003
    Location
    Kirkland, WA, USA
    Posts
    5

    Re: Sql Server Scheduler -- quesion please

    IF ((SELECT BooleanRunning FROM Foo) = 'Yes')
    --code to execute vb app here
    ELSE
    RAISERROR('VB App Not Running', 16, 1)

    The scheduler will then execute the next time you have it set to run.

    Hope this helps!

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    I believe the solution you are looking for is much simpler than it appears - but your description is a little vague as to what you want this application to do - please be specifc.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post Okey okey...

    I have a VBAPP that does processing. Only 1 iteration of the APP should ever be running. The APP needs to run every 5 minutes from 9am to 5pm. The APP may have a runtime of 1-60 minutes.

    When the APP executes, it sets BooleanRunning='YES' in the table. When the APP finishes it's execution it sets BooleanRunning='NO'


    The SQL Scheduler needs to run every 5 minutes from 9-5. The two Steps are:

    (1) Check BooleanRunning in the table. This is handled by a Stored Procedure unless you can tell me a better way to handle it.

    If BooleanRunning='YES' then fail the step and stop the Scheduler Job (since this means the APP already running). Run this job again 5 minutes later.

    If BooleanRunning='NO' then goto step (2)

    (2) Execute the VBAPP via Command Line (which will set BooleanRunning='YES'). Run this job again 5 minutes later.

    There you have it.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Re: Sql Server Scheduler -- quesion please

    Originally posted by p_brisbine
    IF ((SELECT BooleanRunning FROM Foo) = 'Yes')
    --code to execute vb app here
    ELSE
    RAISERROR('VB App Not Running', 16, 1)

    The scheduler will then execute the next time you have it set to run.

    Hope this helps!

    I can make a Command Line Execution from a Stored Procedure? That would solve my problem completely.

    Currently I have a stored procedure to check if BooleanRunning equals YES or NO, and return a value. This would be so much simpler. I will look this up.

    Thanks.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Why do you need to schedule the vb app as a job - why not just handle the logic within the vb app ? You can also set it up as a windows scheduled task and have the vb app test to see if an instance of it is already loaded. What does the vb app actually do and why do you have to keep invoking it ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •