Developing for 2000 (SQL8) on 2005 (SQL9)

We've started upgrading some of our servers to 2005 from 2000, and that
includes my development machine.
I slightly concerned that I will inadvertantly throw in some 2005 code in my
2000 databases which will screw up the destination database on deployment.
Is there a way of specifying that a DB should be 2000-compliant?
What other pitfalls might I face? Any other good advice?
In relative terms, I'm dealing with small-ish DBs (<100,000 rows) and I
don't think I've been pushing the boundaries with anything I'm doing at the
moment, so I dont expect too many problems, but you never know...
Thanks in advance...
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]
CJM
As far as I know one thing is TOP 100 PERCENT doesn't work in SQL Server
2005
Are you talking about T-SQL enhances in SQL Server 2005?
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:edaDM0OIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> We've started upgrading some of our servers to 2005 from 2000, and that
> includes my development machine.
> I slightly concerned that I will inadvertantly throw in some 2005 code in
> my 2000 databases which will screw up the destination database on
> deployment. Is there a way of specifying that a DB should be
> 2000-compliant?
> What other pitfalls might I face? Any other good advice?
> In relative terms, I'm dealing with small-ish DBs (<100,000 rows) and I
> don't think I've been pushing the boundaries with anything I'm doing at
> the moment, so I dont expect too many problems, but you never know...
> Thanks in advance...
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
|||I keep both versions installed for different kinds of work and simply do not
worry about potential risks, since 2005 features simply do not exist in 2000.
ML
http://milambda.blogspot.com/
|||You can come a bit closer to 2000 compliant by specifying database compatibility level 80 instead of
the default 90. See sp_dbcmptlevel for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:edaDM0OIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> We've started upgrading some of our servers to 2005 from 2000, and that includes my development
> machine.
> I slightly concerned that I will inadvertantly throw in some 2005 code in my 2000 databases which
> will screw up the destination database on deployment. Is there a way of specifying that a DB
> should be 2000-compliant?
> What other pitfalls might I face? Any other good advice?
> In relative terms, I'm dealing with small-ish DBs (<100,000 rows) and I don't think I've been
> pushing the boundaries with anything I'm doing at the moment, so I dont expect too many problems,
> but you never know...
> Thanks in advance...
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Orrb4OIGHA.3036@.tk2msftngp13.phx.gbl...
> CJM
> As far as I know one thing is TOP 100 PERCENT doesn't work in SQL Server
> 2005
> Are you talking about T-SQL enhances in SQL Server 2005?
>
I'm talking about anything I don't know!
Obviously I don't intend to use any of the 2005 enhancements in my 2000
DB's, but I just want to be sure that if I stick to ANSI SQL that will I be
OK... For example, do SQL8 & 9 have compatible data files/backup files - I
often transfer data by restoring a backup on a different server or by copy a
data file and attaching it.
Update: I've just noticed that you can install SQL 2000 alongside 2005, and
I've also spotted the compatability settings. Is it better to try and
develop in the one system (2005) or should I install SQL 2000 alongside it?
(note: I've got a new development machine so I have a 'blank canvas' so to
speak; SQL 2000 is currently not installed)
Thanks
Chris
|||Keeping both versions and doing work in the appropriate one seems safer to
me...
ML
http://milambda.blogspot.com/
|||
> OK... For example, do SQL8 & 9 have compatible data files/backup files - I
> often transfer data by restoring a backup on a different server or by copy
> a data file and attaching it.
No - you cannot restore a database from sql server 2005 to a database in sql
server 2000. Same for detach/attach. I believe the reverse direction is
supported.
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:uZpP%23CQIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> No - you cannot restore a database from sql server 2005 to a database in
> sql server 2000. Same for detach/attach. I believe the reverse direction
> is supported.
>
Sorry - I should have been more clear. I don't expect to restore a 2005 DB
to a 2000 instance, but it appears from what you (and others) say that it
works from 2000 to 2005 which is useful to know.
However, I think I'm erring towards installing 2000 and 2005 side-by-side
instead...
|||I definitely agree that you should have side-by-side and develop on a "pure" 2000. Short story is
that database compatibility level isn't 100%...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:umKq9FQIGHA.3904@.TK2MSFTNGP10.phx.gbl...
> "Scott Morris" <bogus@.bogus.com> wrote in message news:uZpP%23CQIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Sorry - I should have been more clear. I don't expect to restore a 2005 DB to a 2000 instance, but
> it appears from what you (and others) say that it works from 2000 to 2005 which is useful to know.
> However, I think I'm erring towards installing 2000 and 2005 side-by-side instead...
>