While executing a stored procedure created in SQL Server 2008 R2 in SQL Server 2012 and retrieving the data through an Entity Data Model, this error occurs:
The version of SQL Server in use does not support datatype 'datetime2'
The stored procedure is executing successfully in SQL Server Management Studio 2012. But the issue is when coming back to the Entity Data Model.
Anyone encountered this kind of an issue??
What is the compatibility level of that database you're running against?
Check using
SELECT compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseNameHere'
DATETIME2 was introduced in SQL Server 2008 - so if that level is below 100 (which is SQL Server 2008), then the DATETIME2 data type isn't available yet.
If the database was upgraded from a SQL Server 2005 instance, most likely, it's compatibility level is still at 90 (SQL Server 2005) and thus the DATETIME2 datatype isn't available yet.
You can upgrade your database compatibility level to more recent values using:
ALTER DATABASE YourDatabaseNameHere
SET COMPATIBILITY_LEVEL = 100;
where level = 100 is SQL Server 2008 / 2008 R2, and level = 110 is SQL Server 2012
