Reputation: 643
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'
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
Reputation: 3366
You can make a fairly reliable version by doing what SSDT does:
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
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