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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments