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 (https://blog.sqlauthority.com) , MSDN Article.
127 Comments. Leave new
Hi,
I need to restore a SQL 2005 DB in SQL 2000. Is this possible?
Thanks,
Chameera Gardihewa
Hi Pinal,
Can i just detach the db from 2005 and reattach it to 2000.
Is this scenario possible?
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.
I would like to know how we can change the Licence options in 2005 after complition of installation !
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?
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.
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
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
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.
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.
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
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
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…
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
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
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
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
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
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.
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)