SQL database compatiblity level does not include SQL 2005

Posted on 2007-08-12
Last Modified: 2013-11-05
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?
Question by:cheyennep
    LVL 142

    Accepted Solution

    are you sure that the instance you are connected to with this database IS sql server 2005?
    run SELECT @@VERSION, and see:

    Author Comment

    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
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    >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.

    Author Comment

    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.....

    Author Comment

    Do you think it is safe for me to run this script?
    sp_dbcmptlevel [[ @dbname = ] name]
    [ , [ @new_cmptlevel = ] version]

    I'm new and scared.

    Author Comment

    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.  
    LVL 30

    Expert Comment

    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.

    Author Comment

    Thank you all for your help - I am new but so far love the service

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now