Loading ...

Change compatibility level of your SQL Server database | CodeAsp.Net

Change compatibility level of your SQL Server database

 /5
0 (0votes)

In our daily life of software and databases development, there are various tasks that we are required to do. Today I was testing the features of MS SQL Server to change compatibility mode to the databases, so that, I thought it would be nice to write blog about it.

Sometimes we need certain database features and behaviours to be compatible with other version of the MS SQL server database. If you need that, here is the solution how to change the compatibility level.

Firstly, you can do that using the SQL Server Management Studio GUI.

Solution I

1. Open your SQL Server Management Studio (Im working with 2008 version).

2. Connect to the server and open the Databases folder

3. Select your database, with righ click go to Properties.

4. You will see the following window

5. In the Option page to the left, you have Compatibility Level. The options I am available to select from is shown in the next image.

 

6. You can set some Other options in the options window bellow (like update statistics and so on) and click Ok.

With this, I have successfully changed the compatibility mode from MS SQL Server 2008 to MS SQL Server 2005.

 

Solution II

You can do the same using SQL commands, here is the solution:

  ----SQL Server 2008 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel
MySampleDB   , 90;
GO

As you see, we are using the sp_dbcmptlevel system command. The last number 90 is the version of SQL Server database.


Versions of SQL Server databases which can be used:

  • SQL Server 6.0 (60)
  • SQL Server 6.5 (65)
  • SQL Server 7.0 (70)
  • SQL Server 2000 (80)
  • SQL Server 2005 (90)

Hope this has helped someone out there.

 

References:

http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

Comments (2)

   
anehad
nice blog...describes how to change compatibility of sql blog. there are 2 solution given. i have also try it . it is a best way. thanks author.... regards,anehad 
10/19/2010
 · 
by
   
mohan
mohan
hi. i had prob when tryin to execute PIVOT is SQL server. but following ur direction i could make it work. but when i try to run it in the my ASP.Net program im gettin the error sayin change the compatibility level. any ideas? thanks
1/26/2011
 · 
by
  • :*
  • :*
  • :
 *

Top Posts