{"id":251,"date":"2019-01-25T18:00:41","date_gmt":"2019-01-25T18:00:41","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=251"},"modified":"2019-01-15T20:29:12","modified_gmt":"2019-01-15T20:29:12","slug":"wait-for-data-to-be-ready","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/","title":{"rendered":"Wait For Data to be ready"},"content":{"rendered":"<p>When data isn&#8217;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&#8217;t completed in time.<\/p>\n<p>Here are two SQL Server based methods for waiting for data, and raising an alert if it&#8217;s not ready. I&#8217;ve designed them as simple templates which I can easily re-use and customise.<\/p>\n<p>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.<\/p>\n<h1>SQL Iteration<\/h1>\n<p>This uses a simple loop in SQL, there are 5 changes which need to be made:<\/p>\n<ul>\n<li>Variables:\n<ul>\n<li><code>SleepTime<\/code>: How long to sleep for (wait between retries).<\/li>\n<li><code>Iterations<\/code>: The number of times to re-try testing for the data.<\/li>\n<li><code>TargetDate<\/code>: This is the date to wait for data to be ready. Note: This doesn&#8217;t have to be a date, it can be anything related to the test required (but other conditions\/variables will need to be changed).<\/li>\n<\/ul>\n<\/li>\n<li>Tests (both the same):\n<ul>\n<li>Initial Test: Run the test for data being ready before looping\/waiting.<\/li>\n<li>Subsequent test(s): Run the test after waiting, to see if data is now ready.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">BEGIN\r\n    ----------------------------------------------------------------------------\r\n    -- Customizable variables\r\n    ----------------------------------------------------------------------------\r\n    DECLARE @SleepTime   VARCHAR(8) = '00:01:00'; -- HH:MI:ss\r\n    DECLARE @Iterations  INT = 30;\r\n    DECLARE @TargetDate  DATE = GetDate() -1;\r\n    \r\n    ----------------------------------------------------------------------------\r\n    -- Control Variables\r\n    DECLARE @Counter     INT = 1;\r\n    DECLARE @TestDate    DATE;\r\n\r\n    ----------------------------------------------------------------------------\r\n    -- Initial Test\r\n    ----------------------------------------------------------------------------\r\n    SET @TestDate = (SELECT MAX(l.[date]) FROM [DB].[schema].[table \u201c\u201d not found \/]<br \/>\n l);\r\n\r\n    ----------------------------------------------------------------------------\r\n    -- Perform loop\r\n    WHILE (@TestDate IS NULL OR @TestDate &lt; @TargetDate)\r\n    BEGIN\r\n        -- Check if the number of iterations has passed the maximum\r\n        IF @Counter &gt; @Iterations\r\n        BEGIN\r\n            DECLARE @ErrorMessage VARCHAR(MAX);\r\n            SET @ErrorMessage = 'ERROR ' + COALESCE(OBJECT_NAME(@@PROCID), 'Manual Task') +' - Data not ready';\r\n            THROW 51000, @ErrorMessage, 1;\r\n        END\r\n\r\n        -- Sleep for the required time\r\n        PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ' - Sleeping (' + CAST(@Counter AS VARCHAR(9)) + '): ' + COALESCE(CONVERT(VARCHAR(20), @TestDate, 120),'') + ' &lt; ' + COALESCE(CONVERT(VARCHAR(20), @TargetDate, 120),'');\r\n        WAITFOR DELAY @SleepTime;\r\n\r\n        ----------------------------------------------------------------------------\r\n        -- Subsequent test(s)\r\n        ----------------------------------------------------------------------------\r\n        SET @TestDate = (SELECT MAX(l.[date]) FROM [DB].[schema].[table \u201c\u201d not found \/]<br \/>\n l);\r\n        \r\n        ----------------------------------------------------------------------------\r\n        -- Iterate counter\r\n        SET @Counter = @Counter + 1;\r\n    END\r\n\r\n    PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ' - Data Ready : ' + COALESCE(CONVERT(VARCHAR(20), @TestDate, 120),'') + ' = ' + COALESCE(CONVERT(VARCHAR(20), @TargetDate, 120),'');\r\nEND\r\n\r\nGO<\/pre>\n<h1>Agent Job Retry<\/h1>\n<p>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:\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/agent\/job-step-properties-new-job-step-advanced-page?view=sql-server-2017\">Job Step Properties &#8211; New Job Step (Advanced Page)<\/a><\/p>\n<p>Create a Job Step to test for data\/wait.<\/p>\n<h2>Job Configuration<\/h2>\n<p>Set the step type as: T-SQL script.<\/p>\n<p>Under &#8220;Advanced&#8221;, set desired settings for:<\/p>\n<ul>\n<li>Retry Attempts<\/li>\n<li>Retry Interval (minutes)<\/li>\n<\/ul>\n<p>then, use the below as a template for the step.<\/p>\n<h2>T-SQL Step<\/h2>\n<p>This template has only two changes to make (the others from the first example are covered under the Agent Job Step Advanced configuration):<\/p>\n<ul>\n<li>Variable:\n<ul>\n<li><code>TargetDate<\/code>: This is the date to wait for data to be ready. Note: This doesn&#8217;t have to be a date, it can be anything related to the test required (but other conditions\/variables will need to be changed).<\/li>\n<\/ul>\n<\/li>\n<li>Test: The test to perform on the data to see if it&#8217;s ready.<\/li>\n<\/ul>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">BEGIN\r\n    ----------------------------------------------------------------------------\r\n    -- Customizable variables\r\n    ----------------------------------------------------------------------------\r\n    DECLARE @TargetDate  DATE = GetDate() -1;<\/pre>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n&#8212; Control\u00a0Variable DECLARE @TestDate DATE; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- &#8212; Perform Test &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- SET @TestDate = (SELECT MAX(l.[date]) FROM [DB].[schema].[table \u201c\u201d not found \/]<br \/>\n l); &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- &#8212; Raise error if data not ready IF (@TestDate IS NULL OR @TestDate &lt; @TargetDate) BEGIN DECLARE @ErrorMessage VARCHAR(MAX); SET @ErrorMessage = &#8216;ERROR &#8216; + COALESCE(OBJECT_NAME(@@PROCID), &#8216;Manual Task&#8217;) +&#8217; &#8211; Data not ready&#8217;; THROW 51000, @ErrorMessage, 1; END PRINT CONVERT(VARCHAR(20), GetDate(), 120) + &#8216; &#8211; Data Ready : &#8216; + COALESCE(CONVERT(VARCHAR(20), @TestDate, 120),&#8221;) + &#8216; = &#8216; + COALESCE(CONVERT(VARCHAR(20), @TargetDate, 120),&#8221;); END GO<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Here are two SQL Server based methods for waiting for data, and raising an alert if it&#8217;s not ready. I&#8217;ve designed them as simple templates which I can easily re-use and customise.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[7,8,19],"class_list":["post-251","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-sql","tag-t-sql","tag-variables"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Wait For Data to be ready - Rows Across The Lake<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Wait For Data to be ready - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"Here are two SQL Server based methods for waiting for data, and raising an alert if it&#039;s not ready. I&#039;ve designed them as simple templates which I can easily re-use and customise.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-25T18:00:41+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Wait For Data to be ready\",\"datePublished\":\"2019-01-25T18:00:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/\"},\"wordCount\":475,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"SQL\",\"T-SQL\",\"variables\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/\",\"name\":\"Wait For Data to be ready - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2019-01-25T18:00:41+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/01\\\/25\\\/wait-for-data-to-be-ready\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Wait For Data to be ready\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data &amp; Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Wait For Data to be ready - Rows Across The Lake","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/","og_locale":"en_GB","og_type":"article","og_title":"Wait For Data to be ready - Rows Across The Lake","og_description":"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.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/","og_site_name":"Rows Across The Lake","article_published_time":"2019-01-25T18:00:41+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Wait For Data to be ready","datePublished":"2019-01-25T18:00:41+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/"},"wordCount":475,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["SQL","T-SQL","variables"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/","name":"Wait For Data to be ready - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2019-01-25T18:00:41+00:00","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/01\/25\/wait-for-data-to-be-ready\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Wait For Data to be ready"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data &amp; Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=251"}],"version-history":[{"count":4,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/251\/revisions"}],"predecessor-version":[{"id":270,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/251\/revisions\/270"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}