Suppress database change message in SQLCMD.exe

Question

I am writing a script generation script. The script is generating list of DDL statments.

The util_generator.sql generation starts with database connection:

USE [TARGET_DATABASE]
GO
...
Many queries
Followed by print statements
...

Since this is a very long script, with unknown length. We use SQLCMD.exe

sqlcmd -s sa\password -i util_generator.sql -r0 -o util_1.sql

The generated scripts util_1.sql looks something like this:

Changed database context to 'TARGET_DATABASE'.
CREATE TABLE ...
CREATE INDEX ...
CREATE VIEW ...

The problem is the generated script util_1.sql fails on unknown command Changed database context to 'TARGET_DATABASE'.

I need assistance to suppress Changed database context to 'TARGET_DATABASE'. message from SQLCMD.exe

Using SSMS util_generator.sql works good not producing any Changed database context to 'TARGET_DATABASE'. message.

But we cannot run util_generator.sql on SSMS from CLI for unknown length output scripts.

Please asvise how to suppress Changed database context to 'TARGET_DATABASE'. statement in SQLCMD.exe

• Scott Hodgin - Retired: Does your script connect to multiple databases during execution or only one database? If you only run the script against a single database, you could include the -d switch to force sqlcmd to connect to that database.
• Dudi Boy: We always use the same target database. I will test now with -d option.

Answer 1

You can suppress (error) messages with a severity level lower than 10. Add -m10 as startup option.

to the best of my knowledge, there is no direct way for SQLCMD to suppress that particular message. But perhaps suppressing info-type messages (as per above) will cut it?

Or, of course, use the -d switch as suggested by Dudi.

Answer 2

Following comment from Scott Hodgin.

The solution is to remove USE [TARGET_DATABASE] from util_generator.sql script.

And assign the target database via SQLCMD.exe using -d option.

The expected SQLCMD.exe is:

sqlcmd -s sa\password -i util_generator.sql -r0 -o util_1.sql -d TARGET_DATABASE