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,
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
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.
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.
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
— 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
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?
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!
changeing the the compatiblilty will effect the server perfromance
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.
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
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?
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
hi,
can you give the steps to be performed for upgrading sql 2000 to sql 2005
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.
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!
can you help me how to db change MS- Access to
SQLSERVER2005. can you give the steps to performed please
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.
@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 : https://support.microsoft.com/en-us/help/936305/
( You need to register to get Cummulative Pack ).
Hope this helps.
Regards,
IM.
@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
@Mauro,
Regarding, Freetool to monitor Performance of your Server ?
Look at these links.
1.
2.
3.
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 :
How to Install and use SQL Server 2005 Performance Dashboard Report please check this link (screen shot tutorial)
Tutorial Link :
Regards,
IM.