October 7, 2007 at 9:48 am
A customer has asked me if my product is compatible with SQL Server
2005 running in 8.0 Compatibility Mode.
What does he mean? How can I check if my SQL server 2005 is running in 8.0 compatibility mode?
Thanks in advance
Yoav
October 7, 2007 at 8:41 pm
Hi,
Compatibility relies on database level and not server level. When you install SQL2005 all the sys dbs will have compatibility level 90.
You can check the compatibility by
sp_dbcmptlevel 'dbname'
eg. output: The current compatibility level is 90.
Output:
90 - Sql2005
80 - Sql2000
70 - Sql 7.0
65 - Sql 6.5
This can also be cheked by using sp_helpdb. In the compatibility_level column you will get the details
eg. output : master 90
Regards..Vidhya Sagar
SQL-Articles
October 8, 2007 at 12:11 am
Still I'd run SQLUpgradeAdvisor to check the db for possible issues with sql2005 compatibility and try to convice the customer to perform detailed testing with SQL2005 mode !
Keep in mind, if you restore a sql2000 db on a sql2005 unstance, compatibility mode needs to be altered to sql2005 manualy !
Exec sp_dbcmptlevel @dbname = 'yourdb', @new_cmptlevel = 90 ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 8, 2007 at 2:47 am
Hi Yoav,
there are some differences between 2000 and 2005 compatibility mode 80, among these are some of the system views that were previously system tables. Some of their fields are set to null in compatibility mode 80 :(. You could use the new system views however, but that would require some rewrite.
There are also some behavioral differences between 2005 and mode 80, most of these you can read about on http://technet.microsoft.com/en-us/library/ms178653.aspx
Regards,
Andras
October 8, 2007 at 6:21 am
you should be aware, as we're talking about modes, that merely changing the mode to 9.0 is not always the same as creating the database in 2005 native.
I was never able to fully test the code differences but - I encountered databases upgraded to 9.0 from 2000 using attach. As part of a series of tests I scripted out all the objects from the 9.0 upgraded database and ran them into a new blank 2005 database ( no data ) imagine my surpise to recieve a raft of errors and warnings. so be careful.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 9:34 am
There are definitely some strange behaviors in 8.0 mode. Andy Warren reported a few issues with some 9.0 features "bleeding" through into the dbs when in 8.0 mode. I can't remember if he created a 9.0 db and set the mode back or restored an 8.0 db.
October 8, 2007 at 9:39 am
Steve Jones - Editor (10/8/2007)
There are definitely some strange behaviors in 8.0 mode. Andy Warren reported a few issues with some 9.0 features "bleeding" through into the dbs when in 8.0 mode. I can't remember if he created a 9.0 db and set the mode back or restored an 8.0 db.
There are indeed horrible things in compatibility mode. In 80, you can create xml schema collections, you can even create CLR assemblies (but you cannot use them). Ideally compatibility modes should be used only during transition 🙂 (preferably a fast transition)
Andras
October 8, 2007 at 9:26 pm
Upgrade advisor is definitely your friend in this case - even with database compatibility set to 8.0 there's no guarantee that changes to T-SQL, etc. won't break your database (e.g. *=).
Joe
December 8, 2008 at 3:19 am
Also this rather wonderful script which checks stored procedures and functions for incompatibilities:
http://www.sqlservercentral.com/scripts/Compatibility/62093/
The Upgrade Advisor is OK but not sufficiently detailed - I can't get it to do detailed checks on a particular database, this is where the above script come in useful.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply