Migrate SQL Server 2000 to SQL Server 2005 - Part 2

In order to work with SQL Server 2005 environment we have to set this option in our migrated database.
It’s used to govern the new features of SQL Server. Below is the screenshot showing the possible compatibility level of a particular database.
Right click the database - >Properties, you will get the below page,











Compatibility level for different version of SQL Server:
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
Below is the query to retrieve the Compatibility level of the databases in our SQL Server.

Code Snippet

SELECT name, cmptlevel FROM master.dbo.sysdatabases

On executing the above query, below is the output.

If we want to update the update the compatibility level manually we can use the below query,

Code Snippet

EXEC sp_dbcmptlevel AdventureWorks, 80;

Problem Statement:
Consider am having a SQL Server 7.0 database and I have restored it into SQL Server 2005. Am having a stored procedure with *= used instead of left outer join. Similarly = * instead of right outer join. Is it possible for me to work my database as a SQL server 7.0 compatible one in SQL Server 2005 IDE (SSMS)
Solution:
Yes, it’s possible. We have to set the compatibility level of our SQL Server DB to 70

Code Snippet

EXEC sp_dbcmptlevel MyDatabase, 70;

Happy Learning!!!

Regards,

Venkatesan prabu. J