When data isn’t ready, we want to fail and raise an alert/notification so that it can be investigated. Similarly, we might want to wait and try again after a short time because a prior process hasn’t completed in time.

Here are two SQL Server based methods for waiting for data, and raising an alert if it’s not ready. I’ve designed them as simple templates which I can easily re-use and customise.

Note: The templates are designed that the configurable parts are preceded by a comment with a dashed line above and below it, and terminated by a dashed line.

SQL Iteration

This uses a simple loop in SQL, there are 5 changes which need to be made:

• Variables:
  • SleepTime: How long to sleep for (wait between retries).
  • Iterations: The number of times to re-try testing for the data.
  • TargetDate: This is the date to wait for data to be ready. Note: This doesn’t have to be a date, it can be anything related to the test required (but other conditions/variables will need to be changed).
• Tests (both the same):
  • Initial Test: Run the test for data being ready before looping/waiting.
  • Subsequent test(s): Run the test after waiting, to see if data is now ready.

BEGIN
    ----------------------------------------------------------------------------
    -- Customizable variables
    ----------------------------------------------------------------------------
    DECLARE @SleepTime   VARCHAR(8) = '00:01:00'; -- HH:MI:ss
    DECLARE @Iterations  INT = 30;
    DECLARE @TargetDate  DATE = GetDate() -1;
    
    ----------------------------------------------------------------------------
    -- Control Variables
    DECLARE @Counter     INT = 1;
    DECLARE @TestDate    DATE;

    ----------------------------------------------------------------------------
    -- Initial Test
    ----------------------------------------------------------------------------
    SET @TestDate = (SELECT MAX(l.[date]) FROM [DB].[schema].[table “” not found /]

 l);

    ----------------------------------------------------------------------------
    -- Perform loop
    WHILE (@TestDate IS NULL OR @TestDate < @TargetDate)
    BEGIN
        -- Check if the number of iterations has passed the maximum
        IF @Counter > @Iterations
        BEGIN
            DECLARE @ErrorMessage VARCHAR(MAX);
            SET @ErrorMessage = 'ERROR ' + COALESCE(OBJECT_NAME(@@PROCID), 'Manual Task') +' - Data not ready';
            THROW 51000, @ErrorMessage, 1;
        END

        -- Sleep for the required time
        PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ' - Sleeping (' + CAST(@Counter AS VARCHAR(9)) + '): ' + COALESCE(CONVERT(VARCHAR(20), @TestDate, 120),'') + ' < ' + COALESCE(CONVERT(VARCHAR(20), @TargetDate, 120),'');
        WAITFOR DELAY @SleepTime;

        ----------------------------------------------------------------------------
        -- Subsequent test(s)
        ----------------------------------------------------------------------------
        SET @TestDate = (SELECT MAX(l.[date]) FROM [DB].[schema].[table “” not found /]

 l);
        
        ----------------------------------------------------------------------------
        -- Iterate counter
        SET @Counter = @Counter + 1;
    END

    PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ' - Data Ready : ' + COALESCE(CONVERT(VARCHAR(20), @TestDate, 120),'') + ' = ' + COALESCE(CONVERT(VARCHAR(20), @TargetDate, 120),'');
END

GO

Agent Job Retry

If an Agent Job is being used for the automation, it can be configured to detect an error, and retry (after waiting). This is simpler to write code for (and can be easily applied to other test conditions/job step types. The Agent Job configuration is covered as part of the MS documentation: Job Step Properties – New Job Step (Advanced Page)

Create a Job Step to test for data/wait.

Job Configuration

Set the step type as: T-SQL script.

Under “Advanced”, set desired settings for:

• Retry Attempts
• Retry Interval (minutes)

then, use the below as a template for the step.

T-SQL Step

This template has only two changes to make (the others from the first example are covered under the Agent Job Step Advanced configuration):

• Variable:
  • TargetDate: This is the date to wait for data to be ready. Note: This doesn’t have to be a date, it can be anything related to the test required (but other conditions/variables will need to be changed).
• Test: The test to perform on the data to see if it’s ready.

BEGIN
    ----------------------------------------------------------------------------
    -- Customizable variables
    ----------------------------------------------------------------------------
    DECLARE @TargetDate  DATE = GetDate() -1;

—————————————————————————-
— Control Variable DECLARE @TestDate DATE; —————————————————————————- — Perform Test —————————————————————————- SET @TestDate = (SELECT MAX(l.[date]) FROM [DB].[schema].[table “” not found /]
l); —————————————————————————- — Raise error if data not ready IF (@TestDate IS NULL OR @TestDate < @TargetDate) BEGIN DECLARE @ErrorMessage VARCHAR(MAX); SET @ErrorMessage = ‘ERROR ‘ + COALESCE(OBJECT_NAME(@@PROCID), ‘Manual Task’) +’ – Data not ready’; THROW 51000, @ErrorMessage, 1; END PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ‘ – Data Ready : ‘ + COALESCE(CONVERT(VARCHAR(20), @TestDate, 120),”) + ‘ = ‘ + COALESCE(CONVERT(VARCHAR(20), @TargetDate, 120),”); END GO