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.