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 sp_dbcmptlevel command with error that database is in use. He has asked me to write about proper procedure of changing database compatibility which will always work.
First read my previous article SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility as it has explained many details about compatibility.
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.
Above three steps are translated in T-SQL in following three line script. Run this procedure in Query Editor.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
thanks
Hello.
On a 2005 sql restore basic sql 2000 that were compatible with 80, my question is that considerations or soft I can run to verify incompatibilities, and then switch compatibility 90 smoothly.
Thank you
bravo Dave!!
Hi.
I want to wnow that now can i set default user when i create a new database.
Ajay
Hi.
I want to know that now can i set default user when i create a new database.
Ajay
Will there be any problems changing the compatibility level from 2000 to 2005?
Search for Behaviour Changes from One version to Another version in Google
@Paul.
There is free in build tool Upgrade Advisor that comes with SQL Server, it could be downloaded as well from web.
I suggest you run Upgrade Advisor on your database, it will list all issues that you could face after upgrade (changing compatibility from 2000 to 2005).
I suggest not to do this on production databases, run this test, in your Test/Dev Env.
~ IM.
Dear sir
i have problem with this crosstab query .
SELECT studentid,tablenumer,roomid,[SUB001] AS [ភាសារបរទេស(អគ្លេស)],[SUB002] AS វប្បធម៌ទូទៅ,[SUB003] AS ភាសាបាលី
FROM(SELECT studentid,tablenumer,roomid,subjectid,score FROM tbl_studentexamein ) ps
PIVOT(SUM(score)FOR subjectid IN([ភាសារបរទេស(អគ្លេស)],[វប្បធម៌ទូទៅ],[ភាសាបាលី])) as pvt
_but it does not acept the selection of subjectid= SUB001,SUB002,SUB003 ON First select statment
please help me to check this code how to run it with sqlserver 2005?
What was the error you got?
Refer this post and apply the logic
In SQL 2008 we can do it using Alter command
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
I have changed the compatability level of sql 2005 to sql 2000
ALTER DATABASE TestDB SET SINGLE_USER;
EXEC sp_dbcmptlevel TestDB, 80;
ALTER DATABASE TestDB SET MULTI_USER;
Now when Iam trying to add “xml ” datatype it is getting added for a column which should not right?
please specify what is the problem
Hi,
i have a production server with sql server express 2005 and need to move database on sql server 2008 workgroup edition.
1.) just want to check can we move sql server express 2005 database to sql server 2008 workgroup edition server?
2.) will there be any impact on the database due to same?
Thanks & Rgds
Deepak Bansal
[email address removed]
If I have no. of stored procedures in my database which are joined with an old style (*=, =*) for right or left joins, how can i convert them automatically instead of converting the each sp code manually.
Is there anyway to do that ?
Some databases on SQL 2008 R2 are in 90 compatibility mode, before I change it to 100, I would like to know what will break, so I ran Upgrade advisor for SQL 2008 R2 but it is giving bunch of errors like below:
Method not found: ”System.Collections.Generic.IEnumerable`1 Microsoft.SqlServer.Management.SqlParser.SqlCodeDom.SqlCodeObject.get_Errors()”.