Learn About Mssql Database Table Compatibility

Recently I was developing a c# application that uses sqlserver2005 database (express edition) setup on my laptop which has win7 os.

The problem is the public server where I will deploy the application is only running a mssql server 2000 database.

My application uses entity framework and I just found out that it optimize the compilation based on the active database connection (which in my local is 2000).

I'm unable to install SQL2K in my laptop since it's 32bit. Fortunately I found this solution:

1.) Open your Microsoft SQL Server Management Studio

2.) Right click the database you want to set compatible to 2K, then select properties

3.) Select the "Options" tab, see printscreen below.

4.) In "Compatibility level:" input select -> "SQL Server 2000 (80)"

You can also do it via script:

EXEC sp_dbcmptlevel AdventureWorks, 80;

Database Versions are:
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005

Finally, search for ProviderManifestToken in your edmx file. You will see something like this:

<Schema Namespace="Models.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2000" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

changed the value to whatever version of SQL Server you need.