Compatibility level, SQL 2005

In SQL Server 2005 it is possible to set a compatibility level.
Prior to this server we were running SQL 2000. Wil still have one
linked server running SQL 2000.
What I'm about: what does this comp. level actually do? When
is it an advantage - and when not?
Regards /SnedkerMorten Snedker (morten_spammenot_ATdbconsult.dk) writes:
> In SQL Server 2005 it is possible to set a compatibility level.
> Prior to this server we were running SQL 2000. Wil still have one
> linked server running SQL 2000.
> What I'm about : what does this comp. level actually do? When
> is it an advantage - and when not?
Use it only if you have legacy code that does not compile in SQL 2005, and
you don't find it worth the effort to fix the code. (Or you can't, because
it's a 3rd party app.)
I don't remember on the top of my head if there are any run-time differences
between compatibility levels 80 and 90. (There are between 60/65 and the
others.)
Note that if you go with compatibility level 80, there may be new features
that will not be available to you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Check Books Online for SQL Server 2005, sp_dbcmptlevel. You'll find an elabo
rate explanation about
what these settings do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Morten Snedker" <morten_spammenot_ATdbconsult.dk> wrote in message
news:qdji82562gpo3ojtlk8oinohdktejkup5r@.
4ax.com...
> In SQL Server 2005 it is possible to set a compatibility level.
> Prior to this server we were running SQL 2000. Wil still have one
> linked server running SQL 2000.
> What I'm about : what does this comp. level actually do? When
> is it an advantage - and when not?
>
> Regards /Snedker|||Erland wrote on Fri, 9 Jun 2006 11:13:55 +0000 (UTC):

> Morten Snedker (morten_spammenot_ATdbconsult.dk) writes:
> Use it only if you have legacy code that does not compile in SQL 2005, and
> you don't find it worth the effort to fix the code. (Or you can't, because
> it's a 3rd party app.)
> I don't remember on the top of my head if there are any run-time
> differences between compatibility levels 80 and 90. (There are between
> 60/65 and the others.)
> Note that if you go with compatibility level 80, there may be new features
> that will not be available to you.
>
And also features like backups - at anything except level 90 you can't back
up your database!
Dan|||Daniel Crichton (msnews@.worldofspack.com) writes:
> And also features like backups - at anything except level 90 you can't
> back up your database!
What? Where did you get that from?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> And also features like backups - at anything except level 90 you can't bac
k up your database!
That would have been a disaster. Backup work find, I just tried below on my
2005 sp2 instance:
exec sp_dbcmptlevel 'pubs', 80
BACKUP DATABASE pubs TO DISK = 'C:\pubs.bak'
Perhaps there is a problem win some of the tools if db compat level is lower
? Like SSMS or Maint
plans?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:e2nNjE9iGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Erland wrote on Fri, 9 Jun 2006 11:13:55 +0000 (UTC):
>
>
> And also features like backups - at anything except level 90 you can't bac
k up your database!
> Dan
>|||Tibor wrote on Fri, 9 Jun 2006 17:01:59 +0200:

> That would have been a disaster. Backup work find, I just tried below on
> my 2005 sp2 instance:
> exec sp_dbcmptlevel 'pubs', 80
> BACKUP DATABASE pubs TO DISK = 'C:\pubs.bak'
> Perhaps there is a problem win some of the tools if db compat level is
> lower? Like SSMS or Maint plans?
Ah, yeah, that was it - SSMS won't allow a maintenance plan for a compat
level 80 db, it doesn't show them in the list of available databases to be
backed up.
Dan|||Erland wrote on Fri, 9 Jun 2006 14:59:51 +0000 (UTC):

> Daniel Crichton (msnews@.worldofspack.com) writes:
> What? Where did you get that from?
>
I restored a 2000 db to 2005, it was compatibility 80, I couldn't run
BACKUP. I switched it to 90, BACKUP runs fine.
Dan|||Daniel wrote to Erland Sommarskog on Mon, 12 Jun 2006 08:36:36 +0100:

> Erland wrote on Fri, 9 Jun 2006 14:59:51 +0000 (UTC):
>
> I restored a 2000 db to 2005, it was compatibility 80, I couldn't run
> BACKUP. I switched it to 90, BACKUP runs fine.
As I replied to Tibor, it was the maintenance wiz in SMSS that won't see the
databases at compatibility 80, not BACKUP itself. Sorry, my memory was a
little hazy on this one.
Dan