August 20, 2014

I build an application using Visual Studio and Sql Server and built the model using Entity Framework but when I deployed the application, I keep getting an error. To give bit more information, I set up the database using Sql Server 2012 and applied the compatibility level to Sql Server 2005 as the database server only had Sql Server 2005. Ya, I know it's an old server but it's not a priority to the business to update that server.

So, after running the application against the production database, I realised that the error is happening from the database server and not from the web server. Basically, it's failing to a add a record when the table has a datetime column and it's failing with the message "The version of SQL Server in use does not support datatype 'datetime2'". I was a bit confused as I thought I have applied the compatibility settings correctly.

To further test, I reloaded the data model from the Sql Server 2005 database but the error was still there.

Now, to fix the issue, I opened the edmx file in an xml editor and within <edmx:StorageModels> element there is "ProviderManifestToken" attribute in "Schema" element. The value for "ProviderManifestToken" was set to 2008. Bingo. I changed that value to 2005 and recompiled the application which fixed the issue.

This is probably a bug in Entity Framework as it not picking up the right database and instead applying a default lowest value.

To apply the compatibility level, right click the database name in SQL Server Management Studio, and click on Properties, navigate to Options and then set compatibility level.

image

0 comments:

Reference: Shahed Kazi at AspNetify.com