SQL Server WAITFOR DELAY and PRINT statement

eiee3dmh 于 2023-08-02 发布在其他

关注(0)|答案(3)|浏览(120)

I have a simple while loop:

DECLARE @Counter INT 

SET @Counter = 1

WHILE (@Counter <= 10)

BEGIN

    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) WAITFOR DELAY '00:00:01'
    SET @Counter  = @Counter  + 1
    
END

I expect that @counter value will be printed out with 1 second delay, but instead, code is executed after 10 seconds and result is printed at the end of the execution in one go.

The counter value is = 1
The counter value is = 2
The counter value is = 3
The counter value is = 4
The counter value is = 5
The counter value is = 6
The counter value is = 7
The counter value is = 8
The counter value is = 9
The counter value is = 10

How to make sure that every next value is printed out with 1 second delay, so I can monitor progress?

Just some backgroud. I would like to use similar script to monitor backup and restore progress. E.g. assign initial value of progress percentage to:

SELECT @Counter = percent_complete
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

and monitor progress in while loop every 5 seconds till percent_complete reaches 100%.

sql-server

来源：https://stackoverflow.com/questions/76661259/waitfor-delay-and-print-statement

3条答案

按热度按时间

9bfwbjaz1#

Aside from the advice already in the comments, try this modified script to demo how you can use raiserror for the behaviour you are expecting in your example code.

Remember to select the Messages table to view output.

declare @Counter Int = 1;

while (@Counter <= 10)
begin
  raiserror ('The counter value is %d', 0, 1, @Counter) with nowait;
  waitfor delay '00:00:01';
  set @Counter += 1;
end;

赞(0）回复(0）举报 2023-08-02

i86rm4rw2#

You say
I would like to use similar script to monitor backup and restore progress

Don't use this loop at all. Just use the STATS option, where you can specify a percentage change to show an update. This is what all backup tools use to monitor progress.

BACKUP DATABASE
......
WITH STATS = 1;

赞(0）回复(0）举报 2023-08-02

unhi4e5o3#

One solution is mentioned in How to show progress in a batch file using sqlcmd? . Other solution is to use Powershell.

GO
CREATE PROCEDURE [scutility].[SqlCmdTestOutput]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count INT = 50
    DECLARE @msg VARCHAR(8000)

    WHILE @count > 0
    BEGIN
        SET @msg = convert(VARCHAR(50), sysdatetime())

        RAISERROR (@msg,0,1) WITH NOWAIT

        WAITFOR DELAY '00:00:10'

        SET @count -= 1
    END
END

And to call this procedure use Invoke-Sqlcmd.

powershell.exe -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"

More details here - https://dba.stackexchange.com/questions/222054/how-to-report-stats-on-restore-via-sqlcmd

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server WAITFOR DELAY and PRINT statement

3条答案

相关问题

热门标签

最新问答