SQL2005 - Database Compatibility Change

[GertjanA]

Hello all,

We've moved databases (EV80SP5) from SQL2000 to SQL2005 without issues.

checking properties in SQL2005, the Compatibility Level is set to 80 for the EV databases.

Can I change this on the properties of the database(s)? Or do I need to run a script of some sort?

Thanks.

Gertjan

[RahulG]

Yes you can simply change it from SQL management studio by gogin to the properties of the database.No script is required as such.

The database compatibility level will remain at "80", ie SQL 2000 level, unless you manually alter it.

This can cause some issues with Enterprise Vault stored procedures, so after an upgrade from SQL 2000 to SQL 2005, you should set the compatibility level to "90", ie SQL 2005.

[Mohawk_Marvin]

Gertjan, I have seen some talk on the drop down not always doing the right thing. I always use this:

http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

[GertjanA]

Hello Mohawk,

I'm talking about Database Compatibility level, not Collation.

The collation is ok, it is the same (fortunately)

The compatibility is not going to give problems now, but will be if we upgrade to 9.x I believe

[Mohawk_Marvin · Accepted solution]

ergh its been a long long week sorry dude

For completeness tho a script for changing compatability is here:

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

change to suit where 

80 is 2000

90 is 2005

100 is 2008.

Again sorry for my totally irrelevant post 

[GertjanA]

For the script! I know the feeling...

[RahulG]

Well I guess changing the compatibiity level from SQl management studio would have not made any difference.

[Rob_Wilcox1]

Note: I've seen a few issues in the past whereby "weird" results (particularly XML processing) was attributed to having SQL 2005 but with the older compatibility level.. so I would definitely recommend if you have SQL 2005 which was an upgrade from SQL 2000 in updating the level.