Try replacing your Create Table statement with the following.
DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = '
CREATE TABLE [MyTestDatabase].[dbo].[MyTestTable]
(
Id INT IDENTITY(1,1),
TestColumn VARCHAR(200),
CONSTRAINT pk_MyTestTable_Id PRIMARY KEY (Id)
)
'
EXEC sp_executesql @statement = @SQLString
Regards
Anthony Tjea
Senior Software Engineer
SOE Software Pty Ltd
Ph: +61 (0) 411 860 914
________________________________
The information transmitted is intended for the person or entity to which it is
addressed and may contain confidential and/or privileged material. Any review,
retransmission, dissemination, copying or other use of, or taking any action in
reliance upon, this information by persons or entities other than the intended
recipient is prohibited. If you have received this in error, please contact the
sender and delete the material from your system. SOE Software Pty Ltd is not
responsible for any changes made to the material other than those made by SOE
Software Pty Ltd or for the effect of the changes on the material's meaning.
From: [email protected] [mailto:[email protected]] On
Behalf Of Conrad Lang
Sent: Thursday, 24 March 2011 9:21 AM
To: ozDotNet
Subject: [OT] SQL Server 2005 Database Update Script to Create a table if the
Database exists and the Table is missing
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
