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
PeterAnother 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|||
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
|||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...
|||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)
No comments:
Post a Comment