Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to start a SQL Server agent job if a table is not empty?

Basically, first I want to check a table, and if the table is not empty then start the job. If it is empty, then check every 1 hour to see whether it has the required information.

I want to run this job once a day once I have data in the table.

share|improve this question

1 Answer 1

Not directly, no. What you describe is kind of like expecting your e-mail program to know when to open and display new e-mail, even though the program needs to be open already to check for new mail.

You might be able to do this with a trigger on the table itself - however note that triggers can be disabled, and also that some operations (such as BULK INSERT) can intentionally bypass triggers.

CREATE TRIGGER dbo.ShouldIEnableJob
ON dbo.TableName
FOR INSERT
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted)
  BEGIN
    UPDATE msdb.dbo.sysjobs 
      SET enabled = 1 
      WHERE name = N'your job name'
      AND enabled = 0;
  END
END

(This doesn't follow your requirement of checking once an hour, but rather it responds to the first insert. You'll also want to drop the trigger once the job has been enabled, but I don't know that you'll be able to do that from within the trigger.)

You could probably create a different job that wakes up every hour. It would check the data, and if it is time to enable the other job, it would do so (similar to the trigger above) and also disable itself. You could also schedule something similar outside of SQL Server (e.g. using PowerShell, VBScript via Windows Task Scheduler, etc.).

Maybe Event Notifications could assist in a similar way, but I've never used them for this specific purpose.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.