The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,360 other followers

The ADO.NET Entity Framework and SQL Server 2000: the ProviderManifestToken attribute and selecting it with XPath

Posted by jpluimers on 2012/05/23

Yes. Dorothy. There are people using the ADO .NET Entity Framework with SQL Server 2000  in parallel of moving towards a more modern Microsoft SQL Server version.

Entity Framework is lovely for developing data-centric applications.

By default, Visual Studio 2010 will target SQL Server 2008 as a database. That is fine, but it is kind of invisible it does: there is no property or dialog where you can change this.

What you have to change in order to have the Entity Framework send SQL Server 2000 compatible queries is to:

  1. Right click your .edmx file
  2. Choose “Open with”
  3. Choose the “XML (text) editor”
  4. Find the  ProviderManifestToken attribute
  5. Change the value (usually from “2008”) into “2000”
  6. Save the .edmx file
  7. Build and run your application

A few caveats:

The ProviderManifestToken is usually at this place:

/edmx:Edmx/edmx:Runtime/edmx:StorageModels/Schema/@ProviderManifestToken

Note this is not a full XPath query to it (as you’d have to add the namespaces see also namespaces in XPath), that would be the one below this post’s signature.

From the ProviderManifestToken documentation:

ProviderManifestToken Schema Attribute

ProviderManifestToken is a required attribute of the Schema element in SSDL. This token is used to load the provider manifest for offline scenarios. For more information about ProviderManifestToken attribute, see Schema Element (SSDL).

SqlClient can be used as a data provider for different versions of SQL Server. These versions have different capabilities. For example, SQL Server 2000 does not support varchar(max) and nvarchar(max) types that were introduced with SQL Server 2005.SqlClient produces and accepts the following provider manifest tokens for different versions of SQL Server.

  • “2000” for SQL Server 2000
  • “2005” for SQL Server 2005
  • “2008” for SQL Server 2008

Note: Starting with Visual Studio 2010, the Entity Data Model Tools do not support SQL Server 2000.

Finally, with SQL Server 2000, be sure to set multipleactiveresultsets=false in your connectionstrings to disable MARS (Multiple Active Result Sets) which SQL Server 2000 does not support.

–jeroen

via: SqlClient for the Entity Framework.

Full XPath query would either have you to add namespaces using an XmlNameSpaceManager instance, or use the full namespace urls like here:

//*[namespace-uri()='http://schemas.microsoft.com/ado/2009/02/edm/ssdl']/@ProviderManifestToken

or more precisely

/*[local-name()='Edmx' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']/*[local-name()='Runtime' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']/*[local-name()='StorageModels' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']/*[local-name()='Schema' and namespace-uri()='http://schemas.microsoft.com/ado/2009/02/edm/ssdl']/@ProviderManifestToken

or on multiple lines:

/*[local-name()='Edmx' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']
/*[local-name()='Runtime' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']
/*[local-name()='StorageModels' and namespace-uri()='http://schemas.microsoft.com/ado/2008/10/edmx']
/*[local-name()='Schema' and namespace-uri()='http://schemas.microsoft.com/ado/2009/02/edm/ssdl']
/@ProviderManifestToken

for this .edmx file (hoping that WordPress doesn’t mess with the xml as it usually does).

<!--?xml version="1.0" encoding="utf-8"?-->

<!-- EF Runtime content -->

<!-- SSDL content -->

Yup, it did it again; please see this file or this pastebin entry for the XML.

–EOM–

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 1,360 other followers

%d bloggers like this: