Hi Conrad, SQL Server attempts to parse, compile and optimise your SQL statement before actually executing the statement .
It is during the compilation step that your SQL statement fails (it does not follow the iterative logic of your IF blocks). Hence it detects that there is a problem because you are referring to a database that does not exist. So in answering your question; No, it is not a "Known Issue", it is simply how SQL Server executes SQL statements. 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 11:03 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 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]<mailto:[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<tel:%2B61%20%280%29%20411%20860%20914> ________________________________ 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]> [mailto:[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<tel:%2B61%20%280%29%20411%20860%20914> ________________________________ 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]> [mailto:[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
