Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Check that what you expected is actually what you got - DB's and SQLCMD

I feel that it is wise move for a script to actually check itself that the database connection that is being used, is infact operating in the intended database.

Occasionally, in the past, I have run scripts on servers where a database was expected to be - except it was not.

Or, the database in the USE statement was not accessible and so the database context had not been switched.

Both of these scenarios will probably result in commands being run in the wrong database, which in turn will either result in more errors or, worse, a whole heap of objects being created in the wrong database, usually master.

So, now I place a simple check after each USE statement, that will verify that the script is operating in the correct database and if not abort.

I've included the script here for reference.

USE SomeDatabase;
GO

IF (DB_NAME() <> 'SomeDatabase')
	BEGIN
		PRINT 'INCORRECT DB';
		RAISERROR ('Cannot run without being in SQLCMD mode',20,1) WITH LOG;
		--RETURN;
	END
ELSE
	BEGIN
		PRINT 'CORRECT DB';
	END
GO

Other times I get scripts that must be run using SQLCMD mode.

SSMS will usually be in TSQL mode by default (although this is possible to change).

If a SQLCMD script is run when SSMS is in TSQL mode then the SQLCMD parts will throw errors, but the TSQL parts of the script will run fine.

This is probably not what you want, especially if SQLCMD is being used to create a database and then the context is being switched to that DB.

Fortunatly, template explorer provides a script that will check if the connection is indeed a SQLCMD one and terminate it if it is simply a TSQL connection. 

This template is provided under the "SQLCMD" folder in SSMS.

I've provided it below just for reference. 

if ('$(SqlCmdIsOn)'<>'Yes')
	BEGIN
		RAISERROR ('Cannot run without being in SQLCMD mode',20,1) with log;
	END
ELSE
	BEGIN
		PRINT('SQLCMD Mode is set to on');
	END
go


Have a good day

Martin.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.