How to stop the “Changed database context to …” me

Question

Is there some way to stop the Changed database context to ... message when the piece of SQL has a USE database in it ?

Answer 1

You need to set the errorlevel of sqlcmd, which defaults to 0. Note: don't confuse the errorlevel here with the exit code of sqlcmd that is returned to, say, cmd.exe as the ERRORLEVEL.

To disable this message for all of an sqlcmd session, use the -m commandline option:

sqlcmd -m 1 <other options>

To disable this message for a block of code, use the :setvar batch command:

USE [mydb]
GO

-- Disable message for next USE command
:setvar SQLCMDERRORLEVEL 1
USE [mydb]
GO

-- Reenable
:setvar SQLCMDERRORLEVEL 0

...

To use the :setvar (or other SQLCMD batch commands) in Management Studio, you need to enable the SQLCMD mode for the query window you're in (menu "Query / SQLCMD Mode"). You'll see that it is enabled, when lines starting with ':' have a gray background.

Answer 2

In my case, an easy and simple solution was to run a small query first, such as SELECT 1;. The message Changed database context... was therefore coupled to this first query and the following queries were retrieved without this error message.

Answer 3

I release updates by having SQLCMD run all my .sql scripts in a directory. But when you start everything with a USE myDB you get a repetitive changed context message in the log file, which is dull. So I use this one liner instead. If the context is actually changed, you still get the message, which is good.

IF EXISTS(SELECT DB_NAME() WHERE DB_NAME() not IN ('myDB')) USE MyDB

Answer 4

Another idea is to use three-parts names in your SQL, e.g. instead of...

USE Pubs; SELECT name FROM dbo.Authors;

...write...

SELECT name FROM Pubs.dbo.Authors;