Downgrade Compatibility level from 90 to 80

Question

Post reply

Downgrade Compatibility level from 90 to 80

baiju-788323

Old Hand

Points: 379
More actions
March 31, 2010 at 1:43 pm

#146250

Hi all...
We have 2 databases on SQL Server 2005 (windows 2003, X32) with compatibility level set to 90.
Now the vendor want to change the compatibility level back to 80, claiming that it is the only level they support.
I have tried, but couldn't find much information about any potential problems about this scenario.
Is there anything, that any of you are aware of, that could go wrong in this situation? Is there anything in particular, that I need to pay special attention to before the change? Since the other database is still going to be at level 90, would it affect that database or any of the system databases?
I am fairly new to sql server, so please help and thank you in advance for all the guidance.
B.

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

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

if you are using any features of SQL 2005 which are not there in 2000 in the database code. It will cause the issue.

Refer to the new features of SQL Server 2005

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I have to concur. If you are using any SQL Server 2005 specific features, like CTE's or the windowing funtions (ROW_NUMBER(), RANK(), etc), DDL triggers, and there are other features, then you will not be able to use compatibility level 80.

baiju-788323 Old Hand Points: 379 More actions · Answer 3

Thanks guys for the prompt response..

This is a canned product, so I don't know what functions they use in their code, but I am assuming they are all compatible since they are the one who is asking me to change.

Anything else?.....

B.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Make sure you have good tested backups and test the downgrade process a few times before you run attempt it in production.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mbricker-600840 SSCertifiable Points: 5468 More actions · Answer 5

I have had to do this a few times when we were upgrading from SQL 2000 to 2005. We had a few applications that were not working with the new DBs on 2005 and setting them back to 2000 compatibilty mode fixed the issues, BUT TEST, TEST, TEST before you try this in production.

Mike

“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey

___________________________________________________________________

Richellere SSC Eights! Points: 902 More actions · Answer 6

I did this in the past and had problems with my code using the new functionalities of SQL Server 2005. I still dont understand why your vendor want to switch to this compatibility level, since SQL Server 2005 support the functions in SQL Server 2000. Most of them are deprecated but they can still be use.

Regards

Richellère

baiju-788323 Old Hand Points: 379 More actions · Answer 7

Thanks guys !!!

Knowing that you guys have done it before successfully, have alleviated a lot of pressure!!!!!

Thanks again......

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events