CODEWITHSUNDEEP
sql-serverssms
Nelda.techspiress
Nelda.techspiress

Reputation: 643

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

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.

Upvotes: 0

Views: 239

Answers (2)

onupdatecascade
onupdatecascade

Reputation: 3366

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

  1. Use SQLCMD mode
  2. Test for SQLCMD mode in case the user forgot to enable it, using SET NOEXEC ON
  3. 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.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31795

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'

Upvotes: 1

Related Questions