Have you tried to print some information message during some loop execution on SQL Server? So statements like
DECLARE @i INT
DECLARE @msg AS VARCHAR(20)
SET @i = 0
WHILE <condition>
BEGIN
SET @msg = 'Updating ' + CAST(@i AS VARCHAR(30))
PRINT @msg
UPDATE TOP(50) myTable SET col1 = 0 WHERE <update condition>
WAITFOR DELAY '000:00:10'
SET @i = @i + 1
END
print out your messages just when your loop completes (or print in a blocks of messages from once).
But this is not what you wanted. You do not see progress of UPDATE statements. The reason for this is because SQL Server does not allow any interactivity during batch execution. Therefore, PRINT statement buffers it’s output.
To get message whithin the loop execution you should use
REISEERROR(…) WITH NOWAITÂ
statement with the severity 10.
So loop will look like:
DECLARE @i INT
DECLARE @msg AS VARCHAR(20)
SET @i = 0
WHILE <condition>
BEGIN
SET @msg = 'Updating ' + CAST(@i AS VARCHAR(30))
RAISERROR(@msg, 10, 0 ) WITH NOWAIT
UPDATE TOP(50) myTable SET col1 = 0 WHERE <update condition>
WAITFOR DELAY '000:00:10'
SET @i = @i + 1
END
On this way you will see messages “Updating 1”, “Updating 2” … during loop execution.