|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problems with my database creation scriptI have a strange situation. I must run the same script in SQL 2000 and SQL 2005. Basically the script does it: IF THE SQL SERVER IS SQL 2005, IT CREATES A NEW DATABASE AND USE IT, BUT IF SQL SERVER IS SQL 2000, IT MUST DOES NOTHING. USE master GO --// Create the DATABASE2005 if SQL is version 2005 IF CAST(LEFT(CAST((SERVERPROPERTY('productversion')) as varchar),1)as int) = 9 --// 8=SQL2000 , 9=SQL2005 BEGIN IF NOT EXISTS (select * from sysdatabases where name ='DATABASE2005') CREATE DATABASE DATABASE2005 ELSE USE DATABASE2005 END .... .... .... // Here I do others things .... .... .... When I run it on SQL 2005 everithing is perfect, in 2000 it works but I receive the following error: Msg 911, Level 16, State 1, Line 8 Could not locate entry in sysdatabases for database 'DATABASE2005'. No entry found with that name. Make sure that the name is entered correctly. This is because the DATABASE2005 will never exists on SQL 2000 Do you know how can I avoid the error message? Thanks This is a parse-time message, that is returned whether or not the USE is
executed. Look at this: if 1=2 use xyz You'll get the same error about couldn't locate database 'xyz'. Can you create a database of that name on your SQL 2000 server and just never use it? Or can you leave with the error? -- Show quoteHTH Kalen Delaney, SQL Server MVP www.solidqualitylearning.com <javier.ignacio.ville***@gmail.com> wrote in message news:1147982570.826464.93690@38g2000cwa.googlegroups.com... > Hi, > > I have a strange situation. > > I must run the same script in SQL 2000 and SQL 2005. > Basically the script does it: > > IF THE SQL SERVER IS SQL 2005, IT CREATES A NEW DATABASE AND USE IT, > BUT IF SQL SERVER IS SQL 2000, IT MUST DOES NOTHING. > > USE master > GO > > --// Create the DATABASE2005 if SQL is version 2005 > > IF CAST(LEFT(CAST((SERVERPROPERTY('productversion')) as varchar),1)as > int) = 9 --// 8=SQL2000 , 9=SQL2005 > BEGIN > IF NOT EXISTS (select * from sysdatabases where name ='DATABASE2005') > CREATE DATABASE DATABASE2005 > ELSE > USE DATABASE2005 > END > ... > ... > ... // Here I do others things > ... > ... > ... > > > > When I run it on SQL 2005 everithing is perfect, in 2000 it works but > I receive the following error: > > Msg 911, Level 16, State 1, Line 8 > Could not locate entry in sysdatabases for database 'DATABASE2005'. No > entry found with that name. Make sure that the name is entered > correctly. > > This is because the DATABASE2005 will never exists on SQL 2000 > > Do you know how can I avoid the error message? > > Thanks > You shouldn't 'have to', I was just trying to come up with ways that the OP
could avoid the error message. Show quote "BurgerKING" <syi***@gmail.com> wrote in message news:1147990015.768418.182120@i40g2000cwc.googlegroups.com... > you shouldn't ever have to create a dummy database. > you don't have a "begin" i the inner if statement.
if not exists (...) begin <------ missing Did you test this?
The BEGIN is not needed if there is only one statement. A BEGIN would yield an error here, because there is no END. Even adding the BEGIN/END around the inner statement would not fix things (and would be unnecessary). The USE dbname is checked at parse time and will give an error no matter how much flow control logic you use. Show quote "BurgerKING" <syi***@gmail.com> wrote in message news:1147989782.030160.322280@j73g2000cwa.googlegroups.com... > you don't have a "begin" i the inner if statement. > > if not exists (...) > begin <------ missing > Just like being in front of the class all over again ;-)
Show quote "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:%23DFMopseGHA.3640@TK2MSFTNGP03.phx.gbl... > Did you test this? > The BEGIN is not needed if there is only one statement. A > BEGIN would yield an error here, because there is no END. Even adding the > BEGIN/END around the inner statement would not fix things (and would be > unnecessary). > > The USE dbname is checked at parse time and will give an error no matter > how much flow control logic you use. > > -- > HTH > Kalen Delaney, SQL Server MVP > www.solidqualitylearning.com > > > "BurgerKING" <syi***@gmail.com> wrote in message > news:1147989782.030160.322280@j73g2000cwa.googlegroups.com... >> you don't have a "begin" i the inner if statement. >> >> if not exists (...) >> begin <------ missing >> > > And you know how much I love that!
Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:eoQ7YtseGHA.3792@TK2MSFTNGP03.phx.gbl... > Just like being in front of the class all over again ;-) > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:%23DFMopseGHA.3640@TK2MSFTNGP03.phx.gbl... >> Did you test this? >> The BEGIN is not needed if there is only one statement. A >> BEGIN would yield an error here, because there is no END. Even adding the >> BEGIN/END around the inner statement would not fix things (and would be >> unnecessary). >> >> The USE dbname is checked at parse time and will give an error no matter >> how much flow control logic you use. >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.solidqualitylearning.com >> >> >> "BurgerKING" <syi***@gmail.com> wrote in message >> news:1147989782.030160.322280@j73g2000cwa.googlegroups.com... >>> you don't have a "begin" i the inner if statement. >>> >>> if not exists (...) >>> begin <------ missing >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics