|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Compatibility level, SQL 2005In 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 confused about: what does this comp. level actually do? When is it an advantage - and when not? Regards /Snedker Check Books Online for SQL Server 2005, sp_dbcmptlevel. You'll find an elaborate explanation about
what these settings do. -- Show quoteTibor 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 confused about: what does this comp. level actually do? When > is it an advantage - and when not? > > > Regards /Snedker Morten Snedker (morten_spammenot_ATdbconsult.dk) writes:
> In SQL Server 2005 it is possible to set a compatibility level. Use it only if you have legacy code that does not compile in SQL 2005, and> > Prior to this server we were running SQL 2000. Wil still have one > linked server running SQL 2000. > > What I'm confused about: what does this comp. level actually do? When > is it an advantage - and when not? 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland wrote on Fri, 9 Jun 2006 11:13:55 +0000 (UTC):
Show quote > Morten Snedker (morten_spammenot_ATdbconsult.dk) writes: And also features like backups - at anything except level 90 you can't back >> 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 confused 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. > up your database! Dan Daniel Crichton (msn***@worldofspack.com) writes:
> And also features like backups - at anything except level 90 you can't What? Where did you get that from?> back up your database! -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland wrote on Fri, 9 Jun 2006 14:59:51 +0000 (UTC):
> Daniel Crichton (msn***@worldofspack.com) writes: I restored a 2000 db to 2005, it was compatibility 80, I couldn't run >> And also features like backups - at anything except level 90 you can't >> back up your database! > > What? Where did you get that from? > 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): As I replied to Tibor, it was the maintenance wiz in SMSS that won't see the > >> Daniel Crichton (msn***@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? >> > I restored a 2000 db to 2005, it was compatibility 80, I couldn't run > BACKUP. I switched it to 90, BACKUP runs fine. databases at compatibility 80, not BACKUP itself. Sorry, my memory was a little hazy on this one. Dan > And also features like backups - at anything except level 90 you can't back 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? -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Daniel Crichton" <msn***@worldofspack.com> wrote in message news:e2nNjE9iGHA.1640@TK2MSFTNGP02.phx.gbl... > Erland wrote on Fri, 9 Jun 2006 11:13:55 +0000 (UTC): > >> Morten 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 confused 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. >> > > > And also features like backups - at anything except level 90 you can't back up your database! > > Dan > Tibor wrote on Fri, 9 Jun 2006 17:01:59 +0200:
>> And also features like backups - at anything except level 90 you can't Ah, yeah, that was it - SSMS won't allow a maintenance plan for a compat >> back 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? level 80 db, it doesn't show them in the list of available databases to be backed up. Dan  |
|||||||||||||||||||||||
Other interesting topics