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
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?
@ 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.
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.
Hi
If I upgrade our server 2000 to 20005 what would happned to the Indexes? is it compatible?
txh so much…..nice article
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
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
@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 :
~ IM.
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.
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
Hi Vijay,
Are you available for a new project in USA.
Please call on [phone removed]
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
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?
Don’t forget to run ‘sp_updatestats’ after you change the DB Compatibility level from 8.0 to 9.0.
I want to upgrade the database from 6.5 to 2008. what are the Steps i have to follow .? Pls help.
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
Hi Pinal,
I want to set up a merge replication from SQL 2005 to SQL 2000. It is giving the below error. Whether I have to change the compatibility of SQL 2005 to SQL 2000 compatibility.
All merge publications in a database must have the same compatibility level. Publication ”does not exist. Changed database context to ”. (.Net SqlClient Data Provider)
Error Number: 21528 Severity: 16 State: 1Procedure: sp_addmergepublication Line Number: 250
my database create on MS-SQL-2000 and attach to MS-SQL-2005, when i run the application on 2000 it works properly but in 2005 it will not work, it give outofmemory.exception.
Hello Pinalji,
Very nice article. I was trying to integrate “ELMAH” in our web sites. There was some error and at the end I identified that I need to change compatibility level of the database. I searched in the google and luckly, got very first link of you in the google list. It helped me in very needed situation.
Thanks a lot.
Hello.
I have a 2k5 server and a database imported from 2K, attached in compatibility level 80.
My question is the following – is there a physical cost to consider when executing those commands?
For example, the database contains a lot of XML-type columns. My concern is that the server would perform some resource-intensive operations on the database (akin to performing a collation change) that would take the important database out of operation for a longer time.
Or is the operation entirely technical and always performed in a matter of seconds?
Is there any tool which will convert SP written in SQL2000 to a compatible SP for MSSQL2005???