WaitFor Delay not working in Azure SQL DW

Question

I am trying to run this simple code in SSMS connected to Azure SQL DW and it fails. I have tried some different variation but none of them seems to be working.

BEGIN
PRINT 'Hello ';
WAITFOR DELAY '00:00:02'
PRINT 'Another';
END

Msg 103010, Level 16, State 1, Line 47
Parse error at line: 2, column: 16: Incorrect syntax near ';'.

Can I ask what the use-case is for this command? Not only is it expensive to run, but it has low max of concurrent queries at 32. Why use thousands of dollars worth of CPU, RAM and SSD just to wait? — wBob, Jan 27 '18 at 15:09
I have stored proc activity which is first activity in the ADF pipeline to resize the ADW Units. i want to wait for this resize to complete before running any Query. This pipeline runs once every day — NSS, Jan 28 '18 at 04:33
You might be better off scaling your warehouse with Powershell cmdlet's , eg `Set-AzureRmSqlDatabase -DatabaseName "yourDW" -ServerName "yourServer" -RequestedServiceObjectiveName "DW400"` — wBob, Jan 29 '18 at 10:26

score 3 · Answer 1 · answered Feb 21 '22 at 21:19

A bloody workaround until we have that simple built-in function:

1- Create a Proc Named "spWait" as follows:

CREATE PROC spWait @Seconds INT
AS
BEGIN

    DECLARE @BEGIN DATETIME
    DECLARE @END DATETIME 

    SET @BEGIN = GETDATE()
    SET @END = DATEADD("SECOND",@Seconds,@BEGIN)

    WHILE (@BEGIN<@END)
    BEGIN   
        SET @BEGIN=GETDATE()
    END

END

2- Call this between your commands

--Do this

EXEC spWait 3

--Do that

score 1 · Accepted Answer · edited Feb 27 '22 at 08:14

Correct. At the moment the WAITFOR statement isn't supported in Azure SQL DW. Note on the documentation for this statement near the top it says whether this statement "applies to" Azure SQL DW.

Please vote for this feature suggestion to help Microsoft prioritize this enhancement.

It may not help you much, but you can connect to the master database under a separate connection and run the WAITFOR statement.

WaitFor Delay not working in Azure SQL DW

2 Answers2