3

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?

Iain Samuel McLean Elder
  • 19,791
  • 12
  • 64
  • 80
  • 1
    Works fine for me. Are you running this in SSMS? If so are you clicking on the messages tab as soon as the query starts running? The focus only gets set to it automatically when the query stops executing. – Martin Smith Dec 16 '13 at 10:49
  • 2
    BTW You can also do `RAISERROR(N'status is %i', 0, 1, @i) WITH NOWAIT;` with no intermediate variable. – Martin Smith Dec 16 '13 at 10:55
  • 2
    @MartinSmith You're right about SSMS. I need the same thing to happen in sqlcmd. Just noticed they behave differently here for me! Edited the question to clarify. – Iain Samuel McLean Elder Dec 16 '13 at 11:00

3 Answers3

3

Is there a way to do this in sqlcmd?

Not as far as I know.

It has already been reported on Connect. See RAISERROR WITH NOWAIT not honoured in SQLCMD11

SQLCMD was rewritten in SQL 2012 to use ODBC. Here is a small regression error that appears to have sneaked in. If you a script which uses RAISERROR WITH NOWAIT, the output is nevertheless buffered. This works correctly with OSQL and SQLCMD from SQL 2008.

but is currently not fixed.

I suppose you could add a SELECT in there of your network packet size (or increase the existing message size) to flush the buffer as a workaround.

For example

DECLARE @i INT = 0;

WHILE @i < 5
  BEGIN
      -- poll async operation status here
      SET @i = @i + 1;

      PRINT 'status is ' + CAST(@i AS VARCHAR(10)) + SPACE(4000);

      WAITFOR DELAY '00:00:01';
  END;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I haven't tested the "workaround" actually works in `sqlcmd`. I suppose you could experiment with sending more and more data but it may just be coded not to display output until the execution completes. – Martin Smith Dec 16 '13 at 11:45
  • Padding the output to be bigger than the network packet size didn't work for me. Replacing PRINT with SELECT didn't work either. Looks like sqlcmd buffers all packets until the end of the batch. osql works, though! See [my answer](http://stackoverflow.com/a/20610954/111424). – Iain Samuel McLean Elder Dec 16 '13 at 12:31
  • "Posted by Microsoft on 24/04/2013 at 10:48 Thanks for the feedback! We will take this into consideration in our future planning" - and they wonder why I hate them – Tim Abell May 29 '15 at 12:27
  • 1
    Seems as though powershell's Invoke-SqlCmd works as expected, as does osql. – Joel Coehoorn Aug 08 '17 at 15:20
3

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.

Iain Samuel McLean Elder
  • 19,791
  • 12
  • 64
  • 80
  • Or another workaround would be to use SSMS in `sqlcmd` mode depending on the motivation for wanting to use `sqlcmd` in the first place. – Martin Smith Dec 16 '13 at 12:34
  • 1
    @MartinSmith The script is designed to run on my deployment server, triggered by new database builds. Command line tools are all I can use here. I've edited the question again to clarify the context. Thanks! – Iain Samuel McLean Elder Dec 16 '13 at 12:44
  • I mark this as accepted because it worked for me. I would use PowerShell instead in future. – Iain Samuel McLean Elder Apr 11 '14 at 12:45
  • osql doesn't support sqlcmd parameters (replacing bits of the sql file) so no good for me, but thanks anyway http://stackoverflow.com/a/3814366/10245 – Tim Abell May 29 '15 at 12:35
1

I was having this issue also and preliminarily (using your example) it would seem that the powershell cmdlet invoke-sqlcmd doesn't have this same issue.

So if you can switch your deploy server to calling a powershell script instead, this may be an option.

However there are some limitations of invoke-sqlcmd compared with sqlcmd so check the docs. http://msdn.microsoft.com/en-us/library/cc281720.aspx. Your mileage may vary.

Using powershell may also simplify the sqlcmdvariable supplying method which is a bit troublesome.. although typically I've now solved that so I'm off to break it all again.

Brett Gerhardi
  • 11
  • 1
  • Can you provide an example in PowerShell? My version of Invoke-Sqlcmd appears to ignore PRINT statements completely. `Invoke-Sqlcmd -Query "PRINT 'Hello, world!'"` produces no output. – Iain Samuel McLean Elder Jul 10 '15 at 13:34
  • You need to add the `-verbose` switch to get print to work `Invoke-Sqlcmd -Query "PRINT 'Hello, world!'" -Verbose`. – JonoB Jan 13 '22 at 13:45