Hi Anthony Thanks. Just wondering though if this is a "known issue" in SQL server with this (sp_executesql) being the known workaround?
On Thu, Mar 24, 2011 at 1:55 PM, Anthony Tjea <[email protected]>wrote: > And to resolve your other issues try the following. > > > > IF db_id('MyTestDatabase') IS NULL > > BEGIN > > PRINT 'Warning: Skipping update because the database does not > exist.' > > END > > ELSE > > BEGIN > > IF OBJECT_ID ('MyTestDatabase.dbo.MyTestTable','U') IS NULL > > BEGIN > > PRINT 'Creating [MyTestDatabase].[dbo].[MyTestTable] table' > > > > 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 > > > > END > > ELSE > > BEGIN > > PRINT 'Warning: [MyTestDatabase].[dbo].[MyTestTable] already > exists.' > > END > > END > > > > > > > > > > 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 *Anthony Tjea > *Sent:* Thursday, 24 March 2011 10:38 AM > *To:* ozDotNet > *Subject:* RE: [OT] SQL Server 2005 Database Update Script to Create a > table if the Database exists and the Table is missing > > > > > > 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 > -- Regards, Conrad Lang Mobile: 04 2260 9718 | Home: 03 9029 2247
