How can I stop SSMS from checking database existence when running a script?

Question

I have a script that checks for the existence of the database and if it doesn't exist exits gracefully with some instructions for the user. However when the database doesn't exist, SSMS flags the USE statement as an error and generates its own error without even running my script. So in the following code, the line

SSTDB doesnot exist. Run 1MakeSSTDB.sql first. Exiting script.

never gets executed. If I comment out the USE SSTDB line, then the script works as expected. Any ideas how to get this to work? (Using SqlServer 2014.)

USE master
GO
IF NOT EXISTS (SELECT name 
 FROM master.dbo.sysdatabases 
 WHERE ('[' + name + ']' = N'SSTDB' OR name = N'SSTDB'))
BEGIN
 Print 'SSTDB doesnot exist. Run 1MakeSSTDB.sql first. Exiting script.'
END
ELSE
BEGIN
 Print 'exists'
 USE SSTDB
END
Print 'done'

Error message from SSMS:

Msg 911, Level 16, State 1, Line 14 Database 'SSTDB' does not exist. Make sure that the name is entered correctly.

Possible duplicate of [USE DB that may not exist](http://stackoverflow.com/questions/14200166/use-db-that-may-not-exist) — Brian Pressler, Dec 23 '15 at 21:16

score 1 · Accepted Answer · edited Dec 24 '15 at 15:41

1

Yeah SSMS always validates the existence of objects even if you used an IF block like this.

One way to do what you want is to use dynamic sql, like this:

USE master
GO
IF NOT EXISTS (SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE ('[' + name + ']' = N'SSTDB' OR name = N'SSTDB'))
BEGIN
    Print 'SSTDB doesnot exist. Run 1MakeSSTDB.sql first. Exiting script.'
END
ELSE
BEGIN
    Print 'exists'
    DECLARE @sql varchar(max) = 
    'USE SSTDB;
    --All code here uses SSTDB database
    '    
    EXECUTE (@sql);
END
--All code here still uses master database
Print 'done'

edited Dec 24 '15 at 15:41

Brian Pressler

6,653
2
19
40

answered Dec 23 '15 at 20:47

Tab Alleman

31,483
7
36
52

Well, yes, I didn't mean to imply that it wouldn't. Everything that depends on the `USE database` will have to be in the same dynamic context. – Tab Alleman Dec 24 '15 at 14:11
Escaping quotes in all that SQL code could be painful, though. I prefer to use SQLCMD and plain T-SQL. – onupdatecascade Dec 24 '15 at 16:15

score 0 · Answer 2 · answered Dec 24 '15 at 16:14

You can make a fairly reliable version by doing what SSDT does:

Use SQLCMD mode
Test for SQLCMD mode in case the user forgot to enable it, using SET NOEXEC ON
Set the whole script to exit on error instead of continuing execution

This is adapted from the SSDT template code:

:on error exit
:setvar dbname SSTDB 

/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following line:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END
GO

IF NOT EXISTS (SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE ( name = N'SSTDB' OR name = N'SSTDB')
) RAISERROR( 'SSTDB doesnot exist. Run 1MakeSSTDB.sql first. Exiting script.', 11, 1 )
GO

PRINT 'Starting script.'

USE $(dbname)
-- Do work

PRINT 'End script'
GO

Also - side issue - the square brackets '[' + name + ']' looks broken. The sysdatabases table does not use them, and you don't have them on the right side of that WHERE condition.

How can I stop SSMS from checking database existence when running a script?

2 Answers2