Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • 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.
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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