Messages from sqlcmd GO vs server's sqlcmd

Question

I have this little SQL script:

CREATE PROCEDURE Something
AS
EXEC SomethingNotExist;

Normally SQL Server Management Studio shows this message:

The module 'Something' depends on the missing object 'SomethingNotExist'. The module will still be created; however, it cannot run successfully until the object exists.

If I put the script above into an .sql file, and run this command from CMD:

SQLCMD -S Server -d DB1 -E -i "Something.sql" -u -I -m 10 -h-1 -l 100 -r1

the two SQLCMD act differently.

The built-in SQLCMD (came with the SQL Server) does not show the message, while SQLCMD GO (https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility) outputs the message.

The message is the #2007 with severity level 10 at https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors-2000-to-2999

I think the written (or not written) message should depend on the command line switch -m. But the two SQLCMD act the same way with every value of -m, both are on their own way.

Why? What is the difference? What do I do wrong?

I also tried --verbosity with SQLCMD GO, but despite of the example in its description, it does not know this switch. Changing the value of -r also didn't bring me the goal. Neither the --driver-logging-level.

Actually my goal would be to not write it out with both SQLCMD, as creating a database with thousands of procedures gives thousands of this message. But if I know the difference I would be happy too.

Answer 1

It seems sqlcmd-go doesn't handle messages and errors the same way: https://github.com/microsoft/go-sqlcmd/blob/main/pkg/sqlcmd/sqlcmd.go#L443

For messages (anything with severity <= 10), ie. MsgNotice, the message is always appended.

For errors, MsgError, s.Format.AddError(m.Error) is called which takes care of the -m parameter while displaying errors.

So, this is maybe(?) a bug or a feature.

SQLCMD original is not without quirks though, because it handles different kind of messages differently:

sqlcmd -m10  -Q "raiserror('test', 10,10); print 'zest'; raiserror('qtest', 1, 1)"
-- outputs
zest

sqlcmd -m1  -Q "raiserror('test', 10,10); print 'zest'; raiserror('qtest', 1, 1)"
-- outputs
zest
qtest

sqlcmd -m1  -Q "create procedure spXYZ AS exec someNonExistingOne;"
-- outputs nothing

It seems that Severity = 10 is translated to severity 0 in the TDS stream, which explains why sqlcmd doesn't display RAISERROR(..., 10, 10). Same goes with the dependency message with error 2007, it's not a PRINT but a RAISERROR internally.

Finally, what's really interesting is that PRINT is always printed by SQLCMD, even though it is transferred through TDS the same way as the RAISERROR (the only difference is that state is 0, so perhaps SQLCMD has some special provisions for PRINTs.

So, in summary, i would say, don't use any of these unless you're aware of the quirks, write your own code which runs files exactly the way you want them to :)