Using SQL Server 2008 and SQL Server 2005 and date time

Question

I've built a entity framework model against a 2008 database. All works ok against the 2008 database. When I try to update the entity on a 2005 database I get this error.

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

I specifically did not use any 2008 features when I built the database. I can't find any reference to datetime2 in the code. And, yes the column is defined as "datetime" in the database.

Solution 1

A quick google points me to what looks like the solution.

Open your EDMX in a file editor (or “open with…” in Visual Studio and select XML Editor). At the top you will find the storage model and it has an attribute ProviderManifestToken. This has should have the value 2008. Change that to 2005, recompile and everything works.

NOTE: You'll have to do this every time you update the model from database.

Solution 2

Quick view of line:

<Schema Namespace="Foobar.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" >

Solution 3

This is very frustrating and I am surprised MS decided not to make it so you could target a given SQL version. To make sure we are targeting 2005 I wrote a simple console app and call it in a PreBuild step.

The prebuild step looks like this:

$(SolutionDir)Artifacts\SetEdmxVer\SetEdmxSqlVersion $(ProjectDir)MyModel.edmx 2005

The code is here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace SetEdmxSqlVersion
{
    class Program
    {
        static void Main(string[] args)
        {
            if (2 != args.Length)
            {
                Console.WriteLine("usage: SetEdmxSqlVersion <edmxFile> <sqlVer>");
                return;
            }
            string edmxFilename = args[0];
            string ver = args[1];
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(edmxFilename);

            XmlNamespaceManager mgr = new XmlNamespaceManager(xmlDoc.NameTable);
            mgr.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2008/10/edmx");
            mgr.AddNamespace("ssdl", "http://schemas.microsoft.com/ado/2009/02/edm/ssdl");
            XmlNode node = xmlDoc.DocumentElement.SelectSingleNode("/edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema", mgr);
            if (node == null)
            {
                Console.WriteLine("Could not find Schema node");
            }
            else
            {
                Console.WriteLine("Setting EDMX version to {0} in file {1}", ver, edmxFilename);
                node.Attributes["ProviderManifestToken"].Value = ver;
                xmlDoc.Save(edmxFilename);
            }
        }
    }
}

Solution 4

Using @Vance's handy console app above, I used the following as a BeforeBuild event

<Target Name="BeforeBuild">
    <!--Check out BD.edmx, Another.edmx, all configs-->
    <Exec Command="$(SolutionDir)\Library\tf checkout /lock:none $(ProjectDir)Generation\DB.edmx" />
    <Exec Command="$(SolutionDir)\Library\tf checkout /lock:none $(ProjectDir)Generation\Another.edmx" />
    <!--Set to 2008 for Dev-->
    <Exec Condition=" '$(Configuration)' == 'DEV1' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\DB.edmx 2008" />
    <Exec Condition=" '$(Configuration)' == 'DEV1' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\Another.edmx 2008" />
    <Exec Condition=" '$(Configuration)' == 'DEV2' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\DB.edmx 2008" />
    <Exec Condition=" '$(Configuration)' == 'DEV2' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\Another.edmx 2008" />
    <!--Set to 2005 for Deployments-->
    <Exec Condition=" '$(Configuration)' == 'TEST' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\DB.edmx 2005" />
    <Exec Condition=" '$(Configuration)' == 'TEST' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\Another.edmx 2005" />
    <Exec Condition=" '$(Configuration)' == 'PRODUCTION' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\DB.edmx 2005" />
    <Exec Condition=" '$(Configuration)' == 'PRODUCTION' " Command="$(SolutionDir)Library\SetEdmxSqlVersion $(ProjectDir)Generation\Another.edmx 2005" />
  </Target>

This is super handy, as avoids annoying redeployment. Thanks for sharing Vance.

I've added TF.exe to the Library solution folder and this helps, as I can now check out the edmx files before trying to edit them, as part of the build. Also I have added this with conditions, so that it sets to 2005 for deployments to the server and back to 2008 for the Dev machine sln configurations. Also to mention you need to add the actual SetEdmxSqlVersion.exe (and .pdb) file(s) to the Library folder (or wherever else you want to keep these bits).

Thanks very much @Vance. Really neat, massive time saver and keeps my builds totally automated and pain free :)

Solution 5

Had a similar problem with 2012 vs. 2008. It can be solved with a BeforeBuild event using XmlPeek and XmlPoke:

   <Target Name="BeforeBuild">
      <XmlPeek XmlInputPath="$(ProjectDir)MyModel.edmx"
               Namespaces="&lt;Namespace Prefix='edmx' Uri='http://schemas.microsoft.com/ado/2009/11/edmx'/&gt;&lt;Namespace Prefix='ssdl' Uri='http://schemas.microsoft.com/ado/2009/11/edm/ssdl'/&gt;"
               Query="/edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema/@ProviderManifestToken">
         <Output TaskParameter="Result" ItemName="TargetedSQLVersion" />
      </XmlPeek>

      <XmlPoke Condition="@(TargetedSQLVersion) != 2008"
               XmlInputPath="$(ProjectDir)MyModel.edmx"
               Namespaces="&lt;Namespace Prefix='edmx' Uri='http://schemas.microsoft.com/ado/2009/11/edmx'/&gt;&lt;Namespace Prefix='ssdl' Uri='http://schemas.microsoft.com/ado/2009/11/edm/ssdl'/&gt;"
               Query="/edmx:Edmx/edmx:Runtime/edmx:StorageModels/ssdl:Schema/@ProviderManifestToken"
               Value="2008">
      </XmlPoke>
   </Target>

If you dislike automated replacement, you can simply replace the XmlPoke task with an Error task.

View more solutions

Comments

[jamiebarrow]

I voted this down by mistake, undid that, but now can't do what I really wanted to do which is vote it up! Thank you for finding the issue. If I understand correctly, does the value change from 2005 to 2008 due to updating the model from the database, where the database is a SQL 2008 DB? In my environment, my developer machine has SQL 2008, but the test environment has 2005 (which production has too). Until we migrate to 2008, am I right in assuming that this will keep occurring?

[Richard Harrison]

I generally set this to 2005, which is the production database; I'm using 2008 for development. 2008 is backwards compatible so no problems. Also this to be changed back after an update/generate. I always validate this when checking in the EDMX after bitter experience.

[Welsh King]

this fix doesnt work for me ?? forums.asp.net/p/1770522/4838628.aspx/…

[MemeDeveloper]

@Vance thanks very much, perfect. A little slow, as I have three edmx files I need to change, so might add a solution config just to revert after a deployment, and remove it from the usual build. Will post an answer now with the info for using this handy tool in a BeforeBuild (or AfterBuild) instead of pre-build. Great appreciated.

[StuartLC]

I think this is OP's problem - he developed against a local SQL 2008 but then deployed to SQL 2005.

[Darcy]

This works unless you don't have access to a SQL 2005 instance.

[Yann Duran]

If this happens in LightSwitch, see my blog post that explains how to fix it in the lsml file (as there's no direct access to the edmx file in LS): lightswitchcentral.net.au/Blog/tabid/83/EntryId/27/…

[Dave Hogan]

It's the only solution but you should be aware that you need to do this each time you amend the edmx as it will revert itself

[Jowen]

A huge disadvantage is that it's a manual step, and thus will be forgotten.

[Andrew Patterson]

With the updates to EF6 (intereceptors etc) have any better solutions than this been developed (ones that are less prone to accidentally forgetting each time the EDMX is updated). So a database first approach, but where we can perhaps intercept the model load and programatically specify the sql version to use?

[Andrew Patterson]

This is along the lines of what I think might be achievable if we knew the right buttons to push.. stackoverflow.com/questions/33282739/…

[admalledd]

This is much better than using an external executable, allows MSBuild to handle all the fancy-ness internally. This can all be easily chained via CallTarget conditional pre-build target tasks depending on publish/build configurations. (EG only change when deploying to a sql2005 environment)

[code4j]

I think set Type System Version=SQL Server 2005 in connection string may also work