SQL SERVER - 2005 Change Database Compatible Level - Backward Compatibility

May 29, 2007 by pinaldave

sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:

   ----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
    ----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

Version of SQL Server database can be one of the following:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

The best practice to change the compatibility level of database is in following three steps.

Set the database to single user access mode by using
```
ALTER DATABASE SET SINGLE_USER
```
Change the compatibility level of the database.
Put the database in multiuser access mode by using
```
ALTER DATABASE SET MULTI_USER
```

Reference : Pinal Dave (http://www.SQLAuthority.com) MSDN Article.

Posted in DBA, Pinal Dave, SQL, SQL Authority, SQL Coding Standards, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 39 Comments

39 Responses to “SQL SERVER - 2005 Change Database Compatible Level - Backward Compatibility”

on July 18, 2007 at 11:00 pm Chameera

Hi,

I need to restore a SQL 2005 DB in SQL 2000. Is this possible?

Thanks,

Chameera Gardihewa
on July 18, 2007 at 11:03 pm pinaldave

Change database compatible level to 80.
Restore the database in sql server 2000 server. Some features of the SQL Server 2005 may not function properly.

SQL SERVER - Restore Database Backup using SQL Script (T-SQL)

Regards,
Pinal Dave (SQLAuthority.com)
on July 25, 2007 at 4:18 am Manish

Hi Pinal,

Can i just detach the db from 2005 and reattach it to 2000.
Is this scenario possible?
on July 25, 2007 at 6:38 am pinaldave

No that is not possible.

Once you go 2005 you can not go back to previous application server. However, you can stay on previous application server and change compatibility as mentioned in this ticket.

Regards,
Pinal Dave (SQLAuthority.com)
on July 27, 2007 at 9:50 pm bubun

I have developed an sql script in sql server2005.It works fine with sql server2005 but fails with sql server2000.
What should I do to make it work?
please suggest.
thanks in advance.
on July 30, 2007 at 6:12 am Praveen Barath

I would like to know how we can change the Licence options in 2005 after complition of installation !
on August 24, 2007 at 7:54 am MarioAl

Hi!

I have installed SQL 2000 and SQL 2005 in my computer. I tried to use xml data type in ‘Microsoft SQL Server Management’, but it caused an error :”Cannot find data type xml.”

So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000:
“Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)”

So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
on August 30, 2007 at 3:08 am Satya SKJ

8.00.194 is with no service pack which is more problematic and perform upto date service pack which is SP4 + hotfixes. To use SQL 2005 references you have to upgrade to 2005 and before that go with UPgrade advisor.
on October 9, 2007 at 6:15 am Ruiduan Wang

Hi Pinal,

Could provide some hint on the problems I encountered. I am using SQL SERVER 2005, my database never requred to change compatibility level until I use Pivot clause recently (so I change the compatibility level to 90). The funny thing is after changing database compatibility level to 90, one of my original stored procedures is not working properly any more. The error message below:
‘Conversion failed when converting the varchar value ‘N ‘ to data type int.’

Many thanks for your kind help
Ruiduan
on October 11, 2007 at 9:22 pm Sony

Hi Pinal,
Can we assure SQL 2000 databases will work as expected in SQL 2005 in 80 compatibility. Is there any known issues for 80 mode in SQL 2005 server
on October 22, 2007 at 3:36 pm Deepak Bhagwat

Hey Pinal,

I am trying to run a query wherein, in a join condition I am equating a GUID column with a VARCHAR column, this query worked fine in SS 2000 but has started failing in SS 2005 (also have an index on this varchar column). So, I tried by removing the index on the column and the query worked in SS2005. Then after reading your article it makes me believe, that, even if I don’t remove the index and set the compatibility level to 80 for SS 2000 the query should start working fine. But you have also mentioned some of the features of SS 2005 might not work, so my 2 questions are -

1) By setting the compatibility to 80 (SS 2000) in SS 2005, will the query work like it did before in SS 2000?
2) If the compatibility is changed to 80, what features in SS 2005 will cease to work?

Thanks in advance..

Deepak Bhagwat.
on November 16, 2007 at 3:39 pm B Samuel

We have migrated our application from SQL 2000 to SQL 2005.
Our application uses DTS packages which uses global variables. This is not working fine. Your suggestions are greatly appreciated.
on November 23, 2007 at 9:13 pm E Yilmaz

Hi,

I tried to restore a database which is backed up from sql 2k5 and compability level is 80 to sql 2k. There is an error:

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. RESTORE DATABASE is terminating abnormally.

The database was running on SQL 2k orginally. It had deattached, and then attached to SQL 2K5. Compability level was 80 and never changed. Now I’m trying to move it to SQL 2K again. But deattach/attach or backup/restore is give error.

Any ideas ?
thanks
on November 27, 2007 at 4:10 pm Hank

Hello Gurus,

Comming from an Oracle environment into Microsoft, after had been assigned a task that is to

See the possibility to migrate our MS SQL Server 2005 running in compatibility level mode 70 into mode 90.

Been told that problem can be raised doing this, does anyone have any info what problem can be raised doing this.

Our company is doing very heavy work based on Micosoft access application link connected to the SQL server,

can there be problems here.

Thx for your advices and hints in advance.

Best regards

Hank
on November 27, 2007 at 10:08 pm Ratheesh.K.Nair

Hi,
Can anyone please tell me the way to migrate data from sybase to sql server 2000.Am not able to find sybase odbc driver for DTS.

Tips are all appreciated..

Thanks in Advance…
on December 21, 2007 at 5:38 pm mayank

Hi Pinal,

I have a problem with SL 2000 version 8.00.194
I am transferring my tables and views from SQL 2005 to SQL 200 through DTS.
After successfull creation of Database and its tables, I am trying to the modify the columns and relations. But the edit/modify option is not enabled in SQL 2000.

Can you help me out for this. How to edit or modify the column properties and relationship.

Thanks
Mayank
on January 13, 2008 at 6:51 pm Suresh

Hi Pinal,

I need to change the Sql2000 MDF file to Sql2005 MDF. is there any feasible reason to change the file from sql2000 to sql2005?. Could you please tell me in detail regarding of this process?

Thanks
Suresh
on January 17, 2008 at 3:33 pm K Sathya Narayanan

hi
i am using SQL Server 8.00.194 . can i transfer the tables & databases from SQL Server 8.00.194 to SQL Server 2005.
is SQL Server 8.00.194 comitible with SQL Server 2005

i have both SQL Server 8.00.194 and SQL Server 2005 installed in my system.

Thanks in Advance
on January 29, 2008 at 1:28 pm puneet

Hi Pinal,
i am working in sql server 2005 …and we hav a huge database in sql2005 of panchkula.and we hav installed it nicely just 1 year back…and now after a year our programmers are facing some problems rearding to connect between database and frontend files ….sometime even they tried to connect the database remotely but they get the TIME OUT EXPRIED ERROR on their systems…but when i went to serever room to check the problem then that works fine on server with all connections ….this problem is increasing on daily basis..please give me a suggestion to handle this..

thanks..and ur suggestions will be highly appericated….

please reply as soon as possible…

puneet singla…huda,panchkula
on February 5, 2008 at 11:59 am Alok

Hi to all!!!
actually i have an problem that one of my application is not working properly in sql server 05 so i decided that we are go through sql server 2000.
but i had i problem that in my system sql 2000 occur and i take a backup from sql 05 from another system…
i am not able to restore or attache that data base in my sql 2000.
if any solution is there plz send me……..
i try this..
EXEC sp_dbcmptlevel gate_pass1, 90;
but it not working..
pl z help me out
alok
on February 28, 2008 at 11:14 am Valentin

Alok,
You should transfer your database from 2005 to 2000 “manually” “piece-by-piece”
Tables and constrains should be recreates, indexes rebuilt, viewes re-created and data taken acress. you can use SSIS for it.
As the author mentiones, once you are on 2005 you are on it — there’s no easy way back, especially when you changed the data, structure, I won’t even mention if you created some SSIS packages, or used new 2005 features, such as BI commands, XML data fields etc — here you are completely stuffed.
on March 6, 2008 at 1:16 am Marcio Evangelista

How to reorg a database with Compatibility Level 70 on SQL Server 2005 using the maintanace plan or command line? (once sql2005 skip the reorg of the index when it is a 7.0 database and I cannot change its compatibility level because the application)
on March 6, 2008 at 11:51 am Rama krishna T

Hi,

Currently we have SQL server 2000 database for past 3 to 4 years. Now planning to move SQL Server 2005. The SQL 2000 database will be restored to 2005 server.
My questions are,

1. The SQL 2000 queries, Stored proc and views will work with SQL server 2005 without any changes?

2. Is there any issues after conversion?

Please help me in this area.

Thanks,
Rama
on March 11, 2008 at 2:06 pm Himanshu

hello,
while tryin to connect Sql Server Studio to server, i get the followin error message:

TITLE: Connect to Server
——————————

Cannot connect to HIMANSHU-PC\SQLEXPRESS.

——————————
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Please suggest me what to do so, to establish a successful connection.
on March 22, 2008 at 4:49 am VJ

Pinal you mentioned —

“Change database compatible level to 80.
Restore the database in sql server 2000 server. Some features of the SQL Server 2005 may not function properly.”

My understanding is its not suppossed to work - as SQL 2005 DB has a different filestructure which SQL2k cant understand.
on March 24, 2008 at 1:07 pm dan

I have a coldfusion 7mx website that is attached to a ms SQL 2000 DB and all works fine, I copied the DB to sql 2005 and now in order to use the same queries I have to add the schema name to the query (was) database.tablename or just tablename and (now) it needs to be database.schema.tablename. I really do not want to find update a few hundred queries what is the best way to fix this?

thanks

-d
on March 25, 2008 at 5:19 pm ali

– Why why sql 2005 server behave like that –

Like command is not doing its job ….??
——————————————————-
1- for simple query like this it is not executing :

select cname from customer
where cname Like ‘%i’

if there is a name ali in the cname col it should return ali

coz the last char in ali is ” i ”

————————————————-

2- where as this query work fine :

select cname from customer
where cname Like ‘i%’

this will return all name start with i .. it work fine but above one will not work why ??

please help brothers >>>

thank you
on March 29, 2008 at 4:15 pm SQL SERVER - 2005 - Change Compatibility Level - T-SQL Procedure Journey to SQL Authority with Pinal Dave

[...] 12, 2008 by pinaldave Six months ago I wrote article about SQL SERVER - 2005 Change Database Compatible Level - Backward Compatibility. Yesterday I received an email asking that one of my blog reader is not able to use the [...]
on June 16, 2008 at 8:29 pm BJ

I need to change the compatibility setting in my MS SQL 5 database to UTF for a specific table. What is the best method of changing this?
on June 19, 2008 at 11:48 pm Cesar Chavero

Hello.

Somebody knows if its possible to change the Compatibility Level automatically (to SQL 2005) from Copy Database Wizard (or some parameters in the comand line of the job?)

I need to copy some databases from SQL 2000 to SQL 2005 and set Compatibility Level to SQL 2005 automatically.

Tanks!
on August 28, 2008 at 4:41 pm santhosh

changeing the the compatiblilty will effect the server perfromance
on September 3, 2008 at 1:23 pm Vicky

Hi,
I have upgraded from 2000 to 2005. Now I want to execute built in encryption function EncryptByPassPhrase() but Im getting the error ”EncryptByPassphrase’ is not a recognized function name.” Is there any compatibility issue.
on September 17, 2008 at 11:43 pm mihir

hi,
i installed sql server 2000 first then i install sql server 2005 with same local server.in sql server 2005 still its pointing to compatibility80 so i delete sql server 2000 so client component are delted of sql server 2000 but server is still there so still my localhost is pointing to compatibility 80.
EXEC sp_dbcmptlevel,90 gets fails it says it supports till80.
Valid values of database compatibility level are 60, 65, 70, or 80.this is the error message need urgetn help
on October 11, 2008 at 1:34 am Kirby

I thought that the compatibility level was to address the way that the TSQL was parsed in statements, but I can’t seem to make it work.

I have a legacy app that is sending a statment to the server that looks like this:

Select Foo
From Bar ( index=abc)

In SQL server 7.0 this works fine, but SQL server 2005 requires that index hints in queries be phrased differently.
I thought that I could set the database to 7.0 compatibility mode and it would parse the statement without errors, but that’s not the case.

So what’s the point of the compatibility level if not for parsing legacy SQL scripts?
on October 21, 2008 at 12:43 pm Hitesh

Hi,

I have installed sql sever 2005 on my machine but on my live server sql server 2000 is installed. So how can I find that my stored procedure written with 2005 version is working with 2000 version.

Please help me
on October 23, 2008 at 12:49 pm srijith

hi,
can you give the steps to be performed for upgrading sql 2000 to sql 2005
on November 5, 2008 at 11:57 pm Sreeni

Tried to restore a SQL Server 2008 database backup to a SQL Server 2005 server. Unfortunately it did not work, even after changing the compatibility level. Please advise.
on November 27, 2008 at 12:53 am Edgar Walther

I have exactly the same problem. My SQL 2008 db is running in 2005 compatibility mode. The backup from that db cannot be restored on my 2005 server. Shouldn’t that be possible? Is there another way to “copy” a sql 2008 db to a 2005 server?
I appreciate your help. Thanks!
on November 28, 2008 at 11:50 pm deepatss

can you help me how to db change MS- Access to
SQLSERVER2005. can you give the steps to performed please