Finally, an easy fix in the .edmx file...

I finally decided to check the .EDMX file after the conversion to SQL 2000 before my commit in SVN. I figured out that the engine is driven by an attribute on the context definition: For SQL 2008, I had:

ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

For SQL 2000:

ProviderManifestToken="2000" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

I guess, I was also not using 2005 when I generated my model either, I was using 2008... :)

ADO.NET Entity Framework SQL2000 vs SQL2005

I have been venturing in upgrading a simple FTP service to ado.net entity framework. As usual with Microsoft, things just don't work out of the box.

First, let me tell you that I started by generating my Model from a SQL 2005 Database. I had to learn the hard way that if you try to use the model with SQL2000, the SQL generated by the entity framework is going to be incompatible in a few cases. This code, which works fine with SQL 2005 didn't work out of the box with SQL 2000:

Dim paramReference As ObjectParameter = New ObjectParameter("p", ThreadContext.Properties()("ActionType"))
Dim actionType As Harmony.FTPService.ActionType = context.ActionType.Where("It.ActionTypeId = @p", paramReference).First()

So then, I decide to try to regen my model on SQL2000 on a different computer where I have SQL 2000 installed to see if that would actually help. So, guess what? After adding my new SQL2000 connection in the ado.net entity wizard, I get this error message:

---------------------------
Microsoft Visual Studio
---------------------------
Could not load file or assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
---------------------------
OK
---------------------------

Ok, thanks to the internet, I found a link that tells me DLL Hell is not a thing of the past and that I got to download and install the following SQL Server 2008 features on top of my SQL 2005/SQL 2000 installation:

Install the following from Microsoft SQL Server 2008 Feature Pack RC0,
June 2008 -
Microsoft SQL Server 2008 Native Client
Microsoft SQL Server System CLR Types
Microsoft SQL Server 2008 Management Objects

These features can be downloaded from:
http://www.microsoft.com/downloads/details.aspx?FamilyId=089A9DAD-E2DF-43E9-9CD8-C06320520B40&displaylang=en

WAIT WHAT? I don't have SQL 2008!!! Oh well, I did install the 3 msi on my machine anyway...

Back in business!!!

I was able to generate my new model based on SQL 2000... At first glance, the only difference is the connection string : MultipleActiveResultSets=False instead of MultipleActiveResultSets=True... Ok, I am not going to venture in the EDMX XML file, it may be different but I don't care at this point in time, I prefer to keep it as a black box since I just barely started my venture in ado.net entity framework.

Guess what? Generating against SQL 2000 fixes the issue!

I hope this post can help anyone else that has SQL2005 vs SQL2005 issues with ADO.NET Entity framework.

Welcome to my Blog

Hi all!

I have created my personal blog where I am going to share my current findings around technology and the like.

Hope you will enjoy it!