Dumb question about compatibility level

msdnzoo


Hi everyone,

This is my first psot so I am hoping for success..

I had been asked to web enable a sql 2000 database. We upgraded to sql 2005 and I imported the data. All went well until a tried to run a CTE ( common table ) routine and it kept on coming back with an error. On searching the web it turns out that the compatibility level must be set to 2005.

When I tried to set the compatibility level the only two options are sql 7 and sql 2000.

I thought it might be due to the fact I had restored from a 2000 database so I tried to create a new database in SSMS but again only sql 7 and 2000 options were available from the drop down box..

What am I doing wrong

Am I not running in sql 2005 at all

Any help would be appreciated

Peter

msdnzoo


Another note..

I tried using

sp_dbcmptlevel DairyManager , 90

and it kept on coming back with :-

'Valid values of database compatibility level are 60, 65, 70, or 80.'

if I tried

sp_dbcmptlevel DairyManager , 80
that worked fine..

Me thinks something is not quite correct here!

Please can someone help as I need to sort this out before any further development

Chris Howarth

Try running the following on your SQL Server instance:

SELECT @@VERSION

All being well then something like the following should be returned:

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)   May  8 2006 22:41:28   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the version returned is 'Microsoft SQL Server 2000 etc...', and if you performed a side-by-side upgrade when you installed the 2005 instance, then you are probably inadvertedly using the old 2000 instance instead of the new 2005 instance - this would account for the problems you are experiencing.

Chris

Jerry Hung

This is on SQL 2005 Server That will be interesting

We restored our SQL2000 database files onto a newly installed SQL2005 Server, change compatibility modes in SSMS to 9.0 (from 8.0) no problem

Helps to list your environment, SQL version, SP1/SP2, blah blah...

GlennAlanBerry

It really sounds like you are running SQL Server 2000. If you did an in place upgrade from 2000 to 2005, the default instance gets upgraded to 2005. If you installed a named instance of 2005 on the same machine where 2000 was running, maybe you are accidently connecting the the default instance that is still running SQL Server 2000.

Running @@VERSION will settle the question. Also the version number of the SQL instance you are connected to will show up in SSMS. If you are in a query window, it will say something like 9.0 SP1 down at the bottom, or in Object Explorer it will say SQL Server 9.0.3042 (for 2005 SP2)