Avatar of Neil Thompson
Neil Thompson
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Cannot set SQL 2005 database to compatability level 80

Hi

Hoping someone can help. Due to old compiled code I am not able to amend a SQL query that wont run on SQL 2005+ (its currently working fine on SQL 2000 but the server and SQL needs to go :) )

I need to get compatibility mode to 80;

I've tried the following which returns an error saying the levels are only 90 upwards, any ideas please?

Neil

I've tried:
ALTER DATABASE corporate
SET COMPATABILITY_LEVEL = 80
GO

Open in new window


message returned:
Msg 15048, Level 16, State 1, Line 3
Valid values of the database compatability level are 90, 100 or 110

Open in new window

Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
Neil Thompson

8/22/2022 - Mon
Steve Wales

Since the error message is telling you you can go up to 110, that tells me you're running SQL 2012.

You can't set compatibility level to 80 in SQL 2012, it's just not supported.

See the matrix of what is supported here: https://msdn.microsoft.com/en-us/library/bb510680.aspx

Time to rewrite that code.
SOLUTION
Vikas Garg

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Neil Thompson

ASKER
Thanks Guys,

Database is definitely 2005
SQL About
I've tried Vikas's code but still get this:
Error
ASKER CERTIFIED SOLUTION
Steve Wales

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Neil Thompson

ASKER
Thanks Steve,  2012 :(

User error, we were connecting via the 2005 server to a 2012 version, rather than the same server's 2005 copy

Stupid mistake, caught by your question.

Thanks
Neil
Your help has saved me hundreds of hours of internet surfing.
fblack61
Neil Thompson

ASKER
Great comments, thanks for the "how to", and the "why it wasn't working" ideas
Neil