Sql-server – Run job in agent based on table data

sql-server-agent

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.

Best Answer

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.

Related Solutions

SQL Server – Run Job Once a Minute Except During Certain Time

I don't think you can work with that single job to get that exact schedule. You might want to consider having another job that disables/enables the job for the period of time that you don't want it to run. Another solution would be to add another conditional logic to your existing job and do a check on the current day of the week and what time it is, and decide whether or not to execute your job's logic.

A few alternatives here, but I think you'll have to get creative with your relatively complex schedule.

Sql-server – Check if SQL Agent job is being executed manually

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END

Best Answer

Related Solutions

SQL Server – Run Job Once a Minute Except During Certain Time

Sql-server – Check if SQL Agent job is being executed manually

Related Question