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://blog.SQLAuthority.com) , MSDN Article.

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | Tagged Database Compatible Level | 59 Comments

59 Responses

on July 18, 2007 at 11:00 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply santhosh

changeing the the compatiblilty will effect the server perfromance
on September 3, 2008 at 1:23 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply deepatss

can you help me how to db change MS- Access to
SQLSERVER2005. can you give the steps to performed please
on January 29, 2009 at 9:09 pm | Reply Mauro

Hi, I have upgraded from 2000 to 2005. Now I notice that all my views containing ORDER BY clause are returning rows in random order. I found that this is a known issue and so I’ve applied the post SP2 fix (My SQL in SP2) related to this problem (3175_316204_intl_i386_zip).
The problem still remain.
Can you help me ?
Thanks

M.
on January 30, 2009 at 9:25 am | Reply Imran Mohammed

@Mauro

By default SQL Server will not allow you to create a view with Order by clause unless you use Top statement. By doing this sql server will not give you syntax error, BUT there is no guarentee that you will get ordered results using the combination of Top and Order by in a view.

Resolution :
You said, applying Service pack 2 solves the problem ??

No, Actually Cummulative Pack 2 of Service Pack2 solves the problem. Meaning after SP2 released, there were cummulative packs as well ( I think current Cummulative pack is 10, which is now called as SP3, anyways). So you need to apply Sp2 and then apply cummulative pack sp2. Hopefully this will solve your issue.

Download Link : http://support.microsoft.com/kb/936305/
( You need to register to get Cummulative Pack ).

Hope this helps.
Regards,
IM.
on February 2, 2009 at 4:48 am | Reply Mauro

@Mohammed

Thank you for your reply.
Next week I will try to install the fix. If this not work I’ll
go ahead with plan N.2 (in other words I’ll change the accounting software in order to fix that problem).

Do you know a freeware tool that I can use to analize the
performace of my server ?

Regards,
Mauro
on February 2, 2009 at 8:27 am | Reply Imran Mohammed

@Mauro,

Regarding, Freetool to monitor Performance of your Server ?

Look at these links.
1. http://www.sqlservercentral.com/articles/Miscellaneous/2959/

2. http://www.sqlservercentral.com/articles/Tools/64908/

3. http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx

What do you mean by analyzing performance of my server.

You mean SQL Server or Machine on which SQL Server is installed.

To Monitor SQL Server 2005, you have a very good performance dashboard report. This report uses DMV’s so there is no overload on the system when this report is running, this report gives you very good information about SQL Server, I personally liked this report, this is available from Microsoft ( free download).

You need to run installation and then you have to run a script in database. ( read instructions on download page).

Download Link : http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

How to Install and use SQL Server 2005 Performance Dashboard Report please check this link (screen shot tutorial)

Tutorial Link : http://www.mssqltips.com/tip.asp?tip=1553

Regards,
IM.
on February 9, 2009 at 10:14 pm | Reply Gezim

I am using an application programmed in sql 2005, this application works perfect in computer where the sql server is installed, but its not working when I share the application to other normal computers.

Can someone help me for solution?
on February 10, 2009 at 5:51 am | Reply Imran Mohammed

@ Gezim,

On the computer where SQL Server 2005 is installed, check the following,

1. SQL Server Browser Service should be running. to check this, go to Start-> Run -> type services.msc a page will open, look for SQL Server Browser Service, if service is stopped then start the service.

2. Check if this issue is because of Windows Firewall, for a minute turn off Windows Firewall and then test you application from other computers, and see if this works. If Windows Firewall is problem, then you have to make an exception.

3. Check if SQL Server allows local anf remote connection in SQL Server Surface Area Configuration. to check this, do the following,
Click Start – > All Programs -> Microsoft SQL Server 2005 – > Configuration Tools – > SQL Server Surface Area Configuration- > Click on links below ( SQL Server Surface Area Configurations Services and Connections ), Expand Your Instance Name – > Expand Database Engine -> Click Remote Connections ( On right hand side of the page) click Local and remote connections and click using Both TCP/IP and Named Pipes.

Last but not the least, this could be a login issue as well, Are you using Windows Authentication or SQL Server Authentication, if you are using Windows Authentication then make sure Windows A/c through which you are trying to login into application, that windows Login has access to SQL Server.

If not then create that Windows login in SQL Server.

If these things does not work, Please post your complete error message here.

Regards,
IM.
on February 10, 2009 at 4:18 pm | Reply HaroonRashed

Hi
iam working on CLR Triggers for Sqlserver2005.my table contains Image field ,when trigger fires it is showing error
–
Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
The context transaction which was active before entering user defined routine, trigger or aggregate “MyTriggerName” has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
on February 24, 2009 at 5:38 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility [...]
on February 26, 2009 at 3:15 am | Reply jwalin

Hi

If I upgrade our server 2000 to 20005 what would happned to the Indexes? is it compatible?
on March 27, 2009 at 11:53 am | Reply putra

txh so much…..nice article
on April 22, 2009 at 9:11 am | Reply ikadewi

Hi Pinal

I’m Ikadewi as newbie for SQL Server 2005,

I’d like ask, to solve the problem of “Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner “.

I used this guidance from geeknets : to change the “Compatibility Level” to SQL Server 2005 (90) since in my database properties can’t direct change through drop down list so I used your guidance : EXEC sp_dbcmptlevel , 90;
GO from SQLQuery command pane.

But then I still have the same problem before I changed the compatibility level.

Do you have any solutions beside above tips since I need to see the database diagram in order to study the relation table in database.

Your feedback review will be much appreciated.
Thank you

Regards,
ikadewi
on May 13, 2009 at 8:37 pm | Reply zaffo69

Hi.. I need move a database from ss2000 to ss2005. I detached the database from 2000 and copied to 2005 and attached to 2005.
The database i ok, but the aplication that use the database give me a error in conversion from varchar to datetime.
The compatibility level in the database is 80.
possible problem of the compatibility with ss2005 ?
thank’s
on May 13, 2009 at 10:53 pm | Reply Imran Mohammed

@zaffo69

Did you first run Upgrade advisor on that database.

Upgrade advisor is a tool that will help you find issue when you move database from sql server 2000 to sql server 2005.

To see a pictorial representation on how to do this, check out this tutorial,

Link : http://www.sql-server-performance.com/articles/dba/sql_server_upgrade_advisor_p1.aspx

~ IM.
on May 15, 2009 at 1:33 pm | Reply Oscar

Hello there, thank you for the information.

I wonder how could I upgrade the SQL Server 7 to 2000 or 2005, will the databases affected? Please advice. Thank you.
on May 19, 2009 at 2:55 am | Reply Vijay Anand Kannan

Hi Pinal,

I am working on changing the compatibility level from 80 to 90. I am trying to see what is going to be affected on changing.

We use lot of Order by Clause which is going to be affected, Also we use sysobjects which will be affected, but I like to know if there is any tool that you can suggest that will scan the entire db and give me the report.

Since my DB is already upgraded to SQL 2005 and set to 80, I can’t use any upgrade advisor for SQL 2005. Suppose if I use the Upgrade advisor for SQL 2008 (we don’t intent to upgrade to SQL 2008) will the results will be affected since the results will be based on SQL 2008.

I appreciate your help. Thanks in Advance

- Vijay Anand Kannan
on June 3, 2009 at 10:38 pm | Reply angelestradamx

good day, i have a problem, I install sql 2005 in my computer but it doesn’t has the opcion compatible level 90 only 80 and old versions.

how do I can activate this opcion?

thank’s
on June 9, 2009 at 9:56 pm | Reply Jorge

Hi I have a SQL 2005 db replicated to another with SQL 2008. I need to change the database compatibility from 2000 to 2005. Are there any knowed issues that may affect my db?
on July 21, 2009 at 10:49 pm | Reply robertg

Don’t forget to run ’sp_updatestats’ after you change the DB Compatibility level from 8.0 to 9.0.
on August 3, 2009 at 4:14 pm | Reply karthikeyan

I want to upgrade the database from 6.5 to 2008. what are the Steps i have to follow .? Pls help.
on October 9, 2009 at 4:34 am | Reply Garyreeds

Hi,

We have upgraded SQL Server 2000 to SQL Server 2005 and everything is working fine. Now we would like to change the compatibility mode to 90.

Could you advice the steps to perform after changing the compatibility mode to 90?

Thank you