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.

Pool

this is a known issue closed as "won't fix" – Equilibrium

Any other command line alternative? – Pool

@MartinSmith - as much as I do not like the answer, it is the answer. Could you arrange your comment as such so I could credit you? – Pool

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

Discant

Clever workaround, thanks. Did it work for you with SQL Server 2017? – Distant

Answer 2

Just to update this is still an issue with sqlcmd.exe in 2022 with the latest sql command line tools (v15.0.2000.5)

However Powershell's Invoke-Sqlcmd in the official SqlServer module allows immediate/realtime logging with sqlcmd for the example above

Import-Module SqlServer
Invoke-Sqlcmd -ServerInstance . -Query "raiserror('hello',10,1) with nowait;waitfor delay '00:00:10';raiserror('world!',10,1) with nowait" -Verbose

Output:

VERBOSE: hello

...10 second delay...

VERBOSE: world!

Note

• the -Verbose flag was required for this message level
• I was using v21.1.18245 of the PowerShell module in PowerShell Core (v7.2.1), though it may work in earlier versions

Accent