how to make raiserror with nowait work with sqlcmd?

Question

Here is the command line:

sqlcmd -S localhost -U myuser -P mypwd -b -r0 -Q "raiserror('hello',10,1) with nowait;waitfor delay '00:00:10';raiserror('world!',10,1) with nowait"

It is supposed to output hello immediately and then after 10 seconds world!. Unfortunately, it outputs both strings after 10 seconds.

Can sqlcmd do it right? How?

P.S.

I do not want to use LinqPad or any GUI for that matter.

Answer 1

I realize this is an old post and there is a high probability that no one will see my answer, but I'll offer something we did at my organization. I'll be the first to admit it's a bit of a kludge, but it has been working well for us.

We have processes that are invoked via SQLCMD and they use Raiserror to indicate progress entries. Obviously that no longer worked when we upgraded to Sql 2012 a long time ago.

I wound up creating a new directory on my C-drive called c:\Sql2008_SQLCMD and I copied the following two files from a Sql 2008 install of Sql Server binn folder to that folder.

• SQLCMD.EXE
• SQLCMD.rll

I then modified my processes (in bat files) to use a dos variable like this

SET SQLCMD="C:\Sql2008_SQLCMD\sqlcmd"

I then substituted any normal SQLCMD command line in my bat file with

%SQLCMD% -S blah...

instead of the normal

SQLCMD -S blah

This technique allowed me to invoke the SQL 2008 version of SQLCMD with a minimum of changes to my bat files scripts.

This allowed me to continue to see the output from Raiserror so I could see the progress of my processes.

I'm currently still using this technique under Sql 2016 CU3 - I'm not sure if I'll have any problems when we eventually go to Sql 2017 or beyound