Problems with my database creation script

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('productv
ersion')) 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?
ThanksThis 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?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<javier.ignacio.villegas@.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('productv
ersion')) 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 don't have a "begin" i the inner if statement.
if not exists (...)
begin <-- missing|||you shouldn't ever have to create a dummy database.|||You shouldn't 'have to', I was just trying to come up with ways that the OP
could avoid the error message.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1147990015.768418.182120@.i40g2000cwc.googlegroups.com...
> you shouldn't ever have to create a dummy database.
>|||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" <syi916@.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 ;-)
"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" <syi916@.gmail.com> wrote in message
> news:1147989782.030160.322280@.j73g2000cwa.googlegroups.com...
>|||And you know how much I love that!
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jerry Spivey" <jspivey@.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...
>