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 changed my SQL 2005 Database dbcmptlevel to 90 using following command
sp_dbcmptlevel WMS001,90
Then I received following error message while compailing a Procedure Using WITH clouse
Msg 319, Level 15, State 1, Procedure spSelectCustomerNew, Line 29
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Why it is Happening…or what is the actual reason for this and what is the solution.?
Hello Mohandas,
Please share your t-sql statement that is causing error.
Regards,
Pinal Dave
Hi,
Please Find the following Proc
CREATE PROCEDURE [dbo].[spSelectCustomer]
@Id decimal(18, 0),
@Name varchar(200),
@Status varchar(50),
@Index int,
@Phone varchar(50),
@StateName varchar(200),
@ZipCode varchar(200),
@Count int OUTPUT
AS
SET NOCOUNT ON
DECLARE @StartIndex int
Declare @EndIndex int
Declare @RowCount int
Declare @RowCountSeed int
set @RowCountSeed=(select isnull(SearchCount,0) from SystemSettings)
table_loop:
SET @StartIndex=(@Index*@RowCountSeed)+1
SET @EndIndex=(@Index*@RowCountSeed)+@RowCountSeed
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (order by C.[CompanyName]) AS ROWID, * FROM [Customer] as C
left join State as S on C.State=S.S_ID
WHERE (C.Id=@Id or @Id=0)
AND (C.[Phone] like dbo.fnGetSearchString(@Phone) or isnull(@Phone,”)=”)
AND (C.[CompanyName] like dbo.fnGetSearchString(@Name) or isnull(@Name,”)=”)
AND (C.[Zip] like dbo.fnGetSearchString(@ZipCode) or isnull(@ZipCode,”)=”)
AND (S.[S_Name] like dbo.fnGetSearchString(@StateName) or isnull(@StateName,”)=”)
and C.IsDeleted = 0 and (Status=@Status or isnull(@Status,”)=”))
SELECT
C.[Id],
isnull(C.[CompanyName],”) as [Name],
isnull(C.[AccountNo],”) as [Account],
isnull(C.[Phone],”) as [Phone],
isnull(S.[S_Name],”) as [State],
isnull(C.[Zip],”) as [ZipCode],
isnull(C.[City],”) as [City]
FROM [Customer ORDERED BY ROWID] as C
left join State as S on C.State=S.S_ID
WHERE ROWID between @StartIndex and @EndIndex
set @RowCount=@@rowcount
set @Count=(select count(*) from [Customer] as C
left join State as S on C.State=S.S_ID
where (id=@Id or @Id=0) AND ([Phone] like dbo.fnGetSearchString(@Phone) or isnull(@Phone,”)=”)
AND ([CompanyName] like dbo.fnGetSearchString(@Name) or isnull(@Name,”)=”)
AND (C.[Zip] like dbo.fnGetSearchString(@ZipCode) or isnull(@ZipCode,”)=”)
AND (S.[S_Name] like dbo.fnGetSearchString(@StateName) or isnull(@StateName,”)=”)
and IsDeleted = 0 and (Status=@Status or isnull(@Status,”)=”)
)
if @RowCount=0 and @Count > 0
begin
set @Index=@Index-1
GOTO table_loop
end
select @Count as RCount
When I put a ( ; ) between the following line it will working fine
SET @EndIndex=(@Index*@RowCountSeed)+@RowCountSeed
; — see this
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (order by C.[CompanyName]) AS ROWID, *
When I put a ( ; ) between the following line it will working fine
SET @EndIndex=(@Index*@RowCountSeed)+@RowCountSeed
; — see this
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (order by C.[CompanyName]) AS ROWID, *
Hi,
I am having a application where the front end is in MS-Access and using SQL 6.5 as the db.
Now that i want to upgrade the database from 6.5 to 2008. so just wanted to know, like is there any pros and cons about migrating the db and the application in 2008.
Thanks & Regards
Anup Kumar
Hi Anup,
You will have to complete the process in two steps. First upgrade to SQL 2000 and then to 2008.
You can use SQL Server’s utilities like upgrade advisor and upgrade wizard to perform this task.
Regards,
Pinal Dave
Thanks Pinal for the response… it helped me a lot.
again related to the above migration process, one more small query:
is there any major/minor changes in DB that has to be done after migration?
in many of the web site that i have visited, says there might be some compatibility issue while migration of the DB. (i mean to say Stored procedure, views, constraints, etc..).
Thanks & Regards
Anup Kumar
Hello Anup,
Before upgrading check the possible issue using upgrade advisor tool provided by Microsoft.
There are different issue for different versions and you must take care those issues in advance for successful migration.
Regards,
Pinal dave
Regarding Best Practice for resetting compatibility mode to previous version. Can you tell us why the db needs to be in single user mode? Is it to prevent data corruption, make the action execute faster or atll, or some other reason?
Thanks you!
use venkat;
ALTER DATABASE venkat SET SINGLE_USER
exec sp_dbcmptlevel venkat,90;
if i run above query ,it will show the following error..
Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.
How do change the compatibility level of that Database?
Yesterday ,one query executed normal time..Today it takes long time for execution..
what are reasons behind that?
rebuild the indexes on your tables and after see the result.
Hi Pinal,
thanx alot for ths gr8 post,it helps me alot and i feel free after use it coz i have a big troble.thanx once again
Hi Pinal,
Can we make a SQL Server 2008 database compatible to 2005 serber by settings the compatibility level ? I tried generating the script from 2008 server with schema and data and then tried to retore it on a 2005 server, but was not fully successful.
If there is any other solutions, plz let me know.
Regards,
Anoop
i have a db on sql server 2005 express and this db work on a LAN workgroup … some sql statments take a very long time to return the required data …… how can i reduce this time?
hints:
1. the database server ia a dell pc (not server) OS windows XP with 3G ram.
2. the sql statments takes a very long time when i use a special table in my sql statment, this table contains about 1,500,000 rows and data size about 500 MB.
waiting ur help
Thanks
Amir M. Guirguis
Amir..what does your quesiton have to do with this blog post? Nothing. you should seek help in the right place. This isn’t it the right place. Ask taht at sqlserver central or on an msdn site.
Hi,
When we need to change the database
compatibility?
Vijay
I upgraded my database from 2000 to 2005 and now wen i try to restore it from a backup to 2008 it gives me an error saying the cast is invalid.
I tried chenging the compatibility but I do not have an option to set compatibility to 90.
Hi,
I was doing some research and wanted to know if it’s possible to run the sql update advisor on SQL 2008. I’d like to see what the effects would be for changing the compatibility from 80 to 100? Anyone tried this? Or is the SSUA meant for checking only sql 2000 or sql 2005 ?
Thanks
Hi Pinal,
We are facing some issues after restoring the SQL 2000 database into SQL 2005.. Some procedures are not functioning properly, that means we are getting a custom error pages when we are accessing certain site pages.. Is it a known issue? Is there any fix for it? Can I fix it by Changing database compatible level to 80 ? Please advise me as soon as possible..
Thank you, Pinal
Kind Regards,
Sujith
There may be some behavioral changes. Can you post the code that created problem?
Hi Madhivanan,
Sorry for the delay, one of the error code is given below
—
where UserId= CAST (@UserId as UniqueIdentifier(16))
—
Please have a check and advise me a fix,
Thank you,
Sujith
Forgot to tell you one thing, I have Changed the database compatible level to 80, but still getting the error page..
Thanks and regards,
Sujith
Are there any variables I can look at like DB size etc to estimate how long I can expect this query to execute? I am changing from 80 to 90, and it has now been executing for almost 10 minutes
ALTER DATABASE mydb SET SINGLE_USER
EXEC sp_dbcmptlevel mydb, 90
GO
ALTER DATABASE mydb SET MULTI_USER
i upgraded sql server 2000 dev ed, to 2005 dev ed with default instance. after upgrade when iam changing
compatibility of northwind database it showing below error. please can u tell the solution for this issue
Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.
Regards,
Kranthi