Hi All
I realise this is a .NET forum but I'm sure we all deal with databases at
some stage. Part of this is creating update scripts for the databases in our
applications.
If you know of a different group I should post my question to, please let me
know.
My scenario is as follows:
- My software uses multiple databases. Some databases are only present if
you've purchased the software component.
- Due to the above, our update scripts should have logic to "skip certain
SQL statements" if the database does not exist.
- I have created the following SQL script that logically should work but
actually results in the following error:
Msg 2702, Level 16, State 2, Line 9
Database 'MyTestDatabase' does not exist.
- Notes:
- The database is a named SQL Server 2005 instance
- I am using SQL Server Management Studio 2008 R2 (although this
should not make a difference).
The initial example update script is as follows:
IF NOT EXISTS (SELECT * FROM [master].[sys].[databases] WHERE name =
'MyTestDatabase')
BEGIN
PRINT 'Warning: Skipping update because the database does not exist.'
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM [MyTestDatabase].[sys].[tables] WHERE name =
'MyTestTable')
BEGIN
PRINT 'Creating [MyTestDatabase].[dbo].[MyTestTable] table'
CREATE TABLE [MyTestDatabase].[dbo].[MyTestTable]
(
Id INT IDENTITY(1,1),
TestColumn VARCHAR(200),
CONSTRAINT pk_MyTestTable_Id PRIMARY KEY (Id)
)
END
ELSE
BEGIN
PRINT 'Warning: [MyTestDatabase].[dbo].[MyTestTable] already exists.'
END
END
What I've found so far:
- If the database exists, the script runs fine (i.e. it creates the table
if it does not exist).
- If the database does not exist, the "IF NOT EXISTS (SELECT * FROM
[MyTestDatabase].[sys].[tables] WHERE name = 'MyTestTable')" statement is
parsed even though the "ELSE BEGIN ... END" block should not be entered.
This results in the "Database 'MyTestDatabase' does not exist." error.
I have found a work-around for this by getting the count of tables and
checking for a count of zero instead of using the "IF NOT EXISTS" statement.
This is shown in the following code sample:
IF NOT EXISTS (SELECT * FROM [master].[sys].[databases] WHERE name =
'MyTestDatabase')
BEGIN
PRINT 'Warning: Skipping update because the database does not exist.'
END
ELSE
BEGIN
DECLARE @tableCount INT
SELECT @tableCount = COUNT(*) FROM [MyTestDatabase].[sys].[tables] WHERE
name = 'MyTestTable'
IF @tableCount = 0
BEGIN
PRINT 'Creating [MyTestDatabase].[dbo].[MyTestTable] table'
CREATE TABLE [MyTestDatabase].[dbo].[MyTestTable]
(
Id INT IDENTITY(1,1),
TestColumn VARCHAR(200),
CONSTRAINT pk_MyTestTable_Id PRIMARY KEY (Id)
)
END
ELSE
BEGIN
PRINT 'Warning: [MyTestDatabase].[dbo].[MyTestTable] already exists.'
END
END
Next Problem:
- The script fails with a similar error but this time it's because of the
"CREATE TABLE" statement which can be verified by commenting out the "CREATE
TABLE" statement and running the script successfully.
- The error is:
Msg 2702, Level 16, State 2, Line 13
Database 'MyTestDatabase' does not exist.
Do you know why I am getting the above error or how to resolve this?
Do you know of a different user group I should post my question to?
Thanks,
Conrad Lang
