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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Neil Thompson

ASKER

Thanks Guys,

Database is definitely 2005

I've tried Vikas's code but still get this:

ASKER CERTIFIED SOLUTION

Steve Wales

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Neil Thompson

ASKER

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