<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
»
compatibility level
27 posts, Page 1 of 3
1
2
3
»
»»
compatibility level
Rate Topic
Display Mode
Topic Options
Author
Message
Erik Hansson
Erik Hansson
Posted Monday, April 28, 2008 5:35 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 21,
Visits: 215
When I upgrade databases from SQL Server 2000 to SQL Server 2005 do I need to change the compatibility level option to SQL Server 2005 after the upgrade?
Is it best practis to change the compatibility level and is it any risk to change the compatibility level?
Post #491295
free_mascot
free_mascot
Posted Monday, April 28, 2008 6:00 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:39 AM
Points: 1,768,
Visits: 1,312
Do not change compatibility level unless and untill you thoroughly test it in your test environment.
Keeping the 2000 compatibility won't hurt anything. But if you will change it to 2005 your Stored Procedures MIGHT give some different results as it will take all settings of 2005.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #491308
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Monday, April 28, 2008 8:50 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 9:00 AM
Points: 31,410,
Visits: 13,728
The database is structured the same in both cases, but in different modes, it behaves fairly closely to that version in terms of code, keywords, etc. I believe that a 80 mode database could still run newer joins available in 90, but they keywords and hints wouldn't work.
Change it only after you've tested things.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #491488
chileu17
chileu17
Posted Monday, April 28, 2008 9:30 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 26, 2011 8:28 AM
Points: 119,
Visits: 400
I have wandering around the net for some answers for a while now but I still have the same questions
what about performance and things like that? does the compatibility level have a impact on this? and what do you mean by saying that some procedures won't have the same results? :S
Thanks
Post #491534
Paul Mu
Paul Mu
Posted Monday, April 28, 2008 9:48 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:42 AM
Points: 339,
Visits: 386
To see examples of what might break between earlier versions of SQL against 2005, try this link: http://geekswithblogs.net/influent1/archive/2007/01/04/102774.aspx
There is one simple example of getting different results depending on compatibility levels right at the bottom of the page.
Paul
Post #491552
jim.powers
jim.powers
Posted Tuesday, April 29, 2008 7:01 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, February 04, 2013 10:00 AM
Points: 1,329,
Visits: 803
I'm currently working on a 2000 to 2005 conversion myself. The biggest thing I have found is that the stored procedures that the vendor wrote do not fully adhere to ANSI standards. For example, I am having to re-write portions of these procs to remove *= and =* in favor of OUTER JOIN. The unfortunate thing is that simply importing the database to 2005 and changing the compatibility mode to 90 won't tell you of these types of problems. You won't know there is a problem until you try to run the proc the first time.
Post #492051
Paul Mu
Paul Mu
Posted Tuesday, April 29, 2008 7:23 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, February 12, 2013 9:42 AM
Points: 339,
Visits: 386
Which is why it is very important to perform an analysis using the migration wizard as a first step!
Paul
Post #492066
Cliff Jones
Cliff Jones
Posted Tuesday, April 29, 2008 7:35 AM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
jim.powers (4/29/2008)
The unfortunate thing is that simply importing the database to 2005 and changing the compatibility mode to 90 won't tell you of these types of problems. You won't know there is a problem until you try to run the proc the first time.
Yes as Paul points out the SQL Server 2005 Upgrade Advisor works very well for this purpose. Also try the BPA (Best Practice Analyzer) as it gives some valuable information as well.
http://msdn2.microsoft.com/en-us/library/ms144256.aspx
Post #492087
jim.powers
jim.powers
Posted Tuesday, April 29, 2008 7:37 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, February 04, 2013 10:00 AM
Points: 1,329,
Visits: 803
The Upgrade Advisor is exactly what told me of the problems that would need to be corrected in advance. Our vendor appears to be useless...
Post #492092
Cliff Jones
Cliff Jones
Posted Tuesday, April 29, 2008 7:51 AM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
Some vendors are like that. But be sure and test thoroughly as there will most likely be things that you encounter that will cause some problems. We have several developers who do not pay attention to column length. SQL 2000 would truncate the data if you tried to insert values larger than the column. SQL 2005 will throw an error. The upgrade advisor will not uncover those types of problems.
Post #492116
« Prev Topic
|
Next Topic »
27 posts, Page 1 of 3
1
2
3
»
»»
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.
