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:
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!