• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

SQL database compatiblity level does not include SQL 2005

I am running SQL Server 2005 with a database that I am guessing was developed in SQL 2000.  If I go into the database options the compatabilty level only goes up to SQL 2000 (80).  I want it to be compatable with 2005.  Mainly because I want to write a PIVOT script.  

How can I "convert" this database from 2000 to 2005?
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are you sure that the instance you are connected to with this database IS sql server 2005?
run SELECT @@VERSION, and see:
cheyennepAuthor Commented:
Here are the results of SELECT @@VERSION:  Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 1)

I feel really stupid.  I swear I am using 2005 because it looks completely different than the 2000 server.  This is what I get when I do "About"
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Maybe I don't understand what you mean by "instance".
Thank you for your help
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess you only installed/used the management studio, which is perfectly possible to connect to sql 2000.
chek your computer if you have several instances installed, the easiest is the computer management -> services list and see what "sql server" services you have there.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aneesh RetnakaranDatabase AdministratorCommented:
>Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
i think you overwrite the exisiting instance of SQL 2000 with the 2005, in such case it will install only the client tools, db engine remains the same.

cheyennepAuthor Commented:
In computer management under Services and Applications it has:
SQL server Configuration>SQL Server 2005 Services
and SQL Server 2005 Network Config and SQL Native Client Config.....
cheyennepAuthor Commented:
Do you think it is safe for me to run this script?
sp_dbcmptlevel [[ @dbname = ] name]
[ , [ @new_cmptlevel = ] version]

I'm new and scared.
cheyennepAuthor Commented:
You guys are right.  The database is version 8.0 under server properties general.  Bummer.  I guess I will have accomplish the PIVOT the old way.  
The new PIVOT is not that helpful, you still have to explicitly list the columns in the statement anyway. Its a bit neater but it isn't dynamic.
cheyennepAuthor Commented:
Thank you all for your help - I am new but so far love the service
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now