<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
compatability level
15 posts, Page 1 of 2
1
2
»»
compatability level
Rate Topic
Display Mode
Topic Options
Author
Message
charipg
charipg
Posted Tuesday, June 30, 2009 12:21 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 AM
Points: 378,
Visits: 897
Hi,
what is cmpt level?
why should we change cmpt level if we move database from sql2000 to sql2005 ?
thanks in advance.....
Post #744192
DNA_DBA
DNA_DBA
Posted Tuesday, June 30, 2009 1:58 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 20, 2013 5:19 AM
Points: 2,374,
Visits: 1,945
As new versions of SQL Server are released, certain features or ways of doing things change. So something that worked in an older version of SQL Server may not work in a newer version or may behave differently. The compatibility level is a way of telling SQL Server to use the older methods of doing things and is applied at a database level. So if the compatibility level is 90 then it will use all the 2005 options, if it is 80 it will allow 2000 features that have been removed from 2005. The compatibility option does not affect the version of the database - so a 2005 database cannot be restored into 2000 as it is still a 2005 database even if the compatibility level is 80. Also, it does not stop you using features that are above the compatibility level you have set - so if you were to set the level to 8 then 2005 features would still be available to you.
As for changing the level after moving a database, it is advisable to keep it at the current level until you have tested the application with the higher level.
Post #744220
klnsuddu
klnsuddu
Posted Tuesday, June 30, 2009 2:02 AM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, July 13, 2010 10:33 AM
Points: 282,
Visits: 757
what is cmpt level?
why should we change cmpt level if we move database from sql2000 to sql2005 ?
cmpt is the database compatibility level.
70 for sql server 7.0
80 for sql server 2000
90 for sql server 2005
100 for sql server 2008.
The compatibility level has to be changed from 80 to 90 when you upgrade from sql server 2000 to sql server 2005 to fully use the new T-SQL features.
You also need to run upgrade adviser before upgrading to sql server 2005, which gives what are things you need to change before upgrade and after upgrade. Accordingly you to change your T-sql code.Some t-sql commands will not be run after you change the cmpt level to 90, which need to modify later.
thanks
Post #744221
charipg
charipg
Posted Wednesday, July 01, 2009 12:03 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 AM
Points: 378,
Visits: 897
thanks..........
Post #745095
Ian Scarlett
Ian Scarlett
Posted Wednesday, July 01, 2009 2:29 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 2:12 AM
Points: 1,322,
Visits: 4,400
DNA (6/30/2009)
Also, it does not stop you using features that are above the compatibility level you have set - so if you were to set the level to 8 then 2005 features would still be available to you.
Not 100% true, it does stop you from doing some things e.g. UDF's weren't available in SQL Server 7, and if you set compatibility to 70 and try to create a function, you get a syntax error.
But, CTE's were introduced in SQL 2005, and if you try to use them with compatibility set to 80, it works!!
Post #745168
charipg
charipg
Posted Thursday, July 02, 2009 7:37 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 AM
Points: 378,
Visits: 897
thanks......
Post #746265
Tom John-342103
Tom John-342103
Posted Monday, March 29, 2010 9:36 AM
Valued Member
Group: General Forum Members
Last Login: Friday, April 26, 2013 8:51 AM
Points: 57,
Visits: 195
Does Microsoft have any plans for discontinuing older compatibility levels? If so, where can this information be found, in particular, dates.
Post #891964
GilaMonster
GilaMonster
Posted Monday, March 29, 2010 2:00 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725,
Visits: 29,981
Yes, but it's not date-based.
SQL Server 2005 does not support compatibility level 65 (SQL Server 6.5)
SQL Server 2008 does not support compatibility level 70 (SQL Server 7)
p.s. Please in future post new questions in a new thread rather than resurrecting an old thread.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #892176
Daveee
Daveee
Posted Tuesday, March 30, 2010 9:00 AM
Say Hey Kid
Group: General Forum Members
Last Login: Friday, May 17, 2013 9:39 AM
Points: 700,
Visits: 1,005
GilaMonster (3/29/2010)
SQL Server 2009 does not support compatibility level 70 (SQL Server 7)
I'm yet to use this version
Post #892889
GilaMonster
GilaMonster
Posted Tuesday, March 30, 2010 9:13 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 37,725,
Visits: 29,981
Please note that was a mistype and should have read SQL Server 2008.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #892909
« Prev Topic
|
Next Topic »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
