Can I print immediately for each iteration in a loop?

Question

My deployment server runs a deployment script for every new database build.

Part of the script blocks to wait for another asynchronous operation to complete.

The blocking code looks like this:

DECLARE @i INT = 0;
DECLARE @laststatus NVARCHAR(MAX) = N'';

WHILE @i < 5
BEGIN
  -- the real delay is longer
  WAITFOR DELAY '00:00:01';

  -- poll async operation status here
  SET @i = @i + 1;

  SET @laststatus = N'status is ' + CAST(@i AS NVARCHAR(MAX));
  RAISERROR(@laststatus, 0, 1) WITH NOWAIT;
END;

It uses the WITH NOWAIT clause of RAISERROR instead of PRINT because it's supposed to print a status update for every iteration.

The deployment server runs the script in sqlcmd with this command:

sqlcmd.exe -i print_test.sql

The output appears all at once like this:

status is 1
status is 2
status is 3
status is 4
status is 5

It should print this after one second:

status is 1

After another second it should print this

status is 2

And so on.

Is there a way to do this in sqlcmd?

Answer 1

You can use osql instead. It's deprecated, but it works as you expect.

The equivalent command is:

osql -E -n -i print_test.sql

osql by default expects a username and password. Use the -E switch to use Windows authentication. This is the opposite of sqlcmd default behavior.

osql by default prints a number for every line in the input file script.

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>

Use the -n switch to suppress the line numbers.

sqlcmd has no -n switch. It just doesn't print line numbers when the -i switch is set.

Martin Smith led me to the workaround by quoting the Microsoft Connect item about this issue.

If you a script which uses RAISERROR WITH NOWAIT, the output is nevertheless buffered. This works correctly with OSQL and SQLCMD from SQL 2008.