Link to home
Create AccountLog in
Avatar of Neil Thompson
Neil ThompsonFlag 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

Avatar of Steve Wales
Steve Wales
Flag of United States of America image

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
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Neil Thompson

ASKER

Thanks Guys,

Database is definitely 2005
User generated image
I've tried Vikas's code but still get this:
User generated image
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Great comments, thanks for the "how to", and the "why it wasn't working" ideas
Neil