Fix: The version of SQL Server in use does not support datatype 'datetime2'.

This message appeared today after posting a new build to our ASP.NET 4 web app:

The version of SQL Server in use does not support datatype 'datetime2'.

The DBA doesn’t use datetime2 because it’s new in SQL Server 2008 and the site runs on 2005. 

It turns out that Entity Framework 4 somehow got the idea to use  SQL Server 2008. The fix was to edit the .edmx file in an XML editor and set the ProviderManifestToken="2005" instead of 2008. (You need to rebuild.) Here’s how the line should look against SQL Server 2005:

<Schema Namespace="OfficeBookDBModel.Store"

Alias="Self" Provider="System.Data.SqlClient"

ProviderManifestToken="2005"

xmlns:store=http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator

xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
 

I’m not sure where EF got the notion to ‘upgrade’ to SQL Server 2008. Perhaps it detected something locally.  I’ve just built a new workstation and installed a CTP of  SQL Server Management Studio Basic  10.50.1352.12. It’s working with the database marked as compatibility level SQL Server 2005 (90).

Anyway, if you hit this error, let’s hope you haven’t lost time like I did!

Ken

5 Comments

  • Thank you verry much! I couldn't find the problem 'till i found this article. I'm using sql2008 on my computer but the server has sql2005 ..
    I couldnt understand why when I use the right datatype it still didnt work for me.
    It really helped! Thanks a lot! It solved my problem!

  • Everyone talking about the .edmx file but not saying where to actually find it. Thanks for adding this detail!

  • make a search in the solution of vs on ProviderManifestToken

  • It IS a really good immediate solution. Thank you :)

  • Dear Anna, The EDMX is usually placed where u have saved your project, unless u move it somewhere.
    Open your solution, Right Click on the EDMX file and then click on Properties, and in the window shown look for Full Path

Comments have been disabled for this content.