Compatibility Mode in SQL server 2005

Question

Compatibility Mode in SQL server 2005

yoavepelman

SSC Journeyman

Points: 93
More actions
October 7, 2007 at 9:48 am

#120640

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
yepelman@hp.com

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

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

Johan Bijnens SSC Guru Points: 135503 More actions · Answer 2

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

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

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

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/

Steve Jones - SSC Editor SSC Guru Points: 737892 More actions · Answer 5

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.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 6

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

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 7

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

Peter Wright SSCommitted Points: 1966 More actions · Answer 8

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.

Compatibility Mode in SQL server 2005

Cookies on SQLServerCentral