Today, I was dumbfounded by LINQ's optimization skills. I built a query over a list of items, which have an assigned category and a display order. I wanted to fetch a list of the distinct categories, to display them as an overview, and have them sorted according to the order of the entries themselves. My LINQ query looked something like this:

var categories = context.Entries.OrderBy(e => e.DisplayOrder).Select(e => e.Category).Distinct();

Everything seemed to work, but when I started ordering things around, the categories would remain as they were. What I also noticed, was that they were ordered alphabetically, which perplexed me. I decided to look at the generated query, and lo and behold, there was not a single ORDER BY clause in it. I took the generated query, added my own order by clause to it, and got an error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Of course: Distinct probably just sorts the values to make filtering out duplicates easier. I left out the Distinct() method, and my ordering was the way I wanted to have it.

I have not yet worked on a sql-based solution for this, since my structure holding the categories handled duplicate filtering by itself. And my data barely has any duplicates in it anyways, so I left it at that.

But what amazed me, was that LINQ actually left out the order by on its own accord, since it would have caused an error. The LINQ to SQL parser really knows, what it's doing, although in my case, I would have liked to get some sort of warning, that the query will not behave as expected. Nonetheless, I love LINQ :-)

Yesterday, I wasted a few hours trying to upgrade my SQL Server 2005 installation to 2008. Actually, upgrading is a simple process. You can simply select, which instance to upgrade, and the installer does the rest. It worked absolutely fine for my regular instance. I got some installation errors for a component, but didn't mind that at first. Everything seemed to be working.

At least I thought so, until I noticed, WHAT component got the errors. The SQL Server Management Studio was not installed. And the older 2005 version isn't able to connect to 2008. Bummer.

So I thought, let's try to reinstall it. Same error: "The error code is 2349." When you copy the details to the clipboard, you at least find the URL http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0xF45F6601%25401201%25401, that gives you the information, that the SQL Server Native Client is causing the trouble.

So I tried upgrading my SQL Server Express instance, but that was also dismissed with an error. Next, I uninstalled my SQL Server 2005 express instance, uninstalled the native client, and now added the Management Studio feature to my SQL Server 2008 instance. Same error, this is really getting frustrating.

So now, my last resort is to uninstall the whole d*rn thing and try to reinstall it. At least the installation only takes about 2 hours *cough*.

UPDATE: Even the complete uninstall did not help. As the error message states, the Native Client is supposedly not installed from the sqlncli.msi package. Well, I even uninstalled and reinstalled ONLY THAT package, to no avail. I'm still getting the same bogus error, when I try to install SSMS. Any help is welcome! I'm out of ideas.

But at least I'm not all alone with my ss2008 install problems. A friend of mine had other issues, but at least he was able to resolve them: http://www.entwicklungsgedanken.de/2009/01/28/installation-problems-with-sql-server-2008/comment-page-1/#comment-252.

UPDATE 2: I have found out, that it's the Visual Studio Tools for Applications 2.0 installer, that is causing the problems. But I still have no idea why.

UPDATE 3: Rollback all rants :-) I found the issue. A file could not be replaced/updated by the installer. This was due to the fact, that my hard disk seems to be defective. The file could not be read, even less replaced. After renaming it, the installation went fine. So now, I have performed a scandisk, the errors have been cleared, and everything is in order. Guess I'll be backing up more often now, though.

We finally got a useful response concerning the TransactionScope Timeout problem. Someone else had a similar issue and started a new thread in the Microsoft forums: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=3069149&SiteID=1. The issue has been resolved, but only as of the .NET Framework 3.5. There is a new connection string parameter named "Transaction binding". This is set to "Implicit unbind", which by default causes the described (mis-)behavior. Instead, you must set it to "explicit unbind", so that queries issued after the transaction times out are still considered to be INSIDE the transaction and not in auto-commit mode. You can read the details in the forum post.

Alas, this only solves the problem when using the SqlClient ADO.NET provider. For all other transactive repositories, the issue still exists.

If you've ever built an ASP.NET application using a User Instance database, you may have come across the problem, that you would have liked to access the database using the Management Studio or similar programs, but just couldn't find the database, because it is in the user instance and not in the database server itself.

I'd like to issue my thanks to Mike of the Sql Server Express Weblog for publishing how it's done. I looked for hours trying to get the database attached to the regular server, but all that is not neccessary. You can directly connect to the user instance by using a named pipe. The original explanation can be found here: http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx. Below are the required steps in short.

• Connect to the server normally.
• Issue a new query:
  SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances
• Locate the user instance you wish to connect to and copy the instance_pipe_name column's value.
• Open a new server connection and use the copied value as the server name.

And presto, you can access everything as you would in the regular server.

Note: The pipe name is generated, when the user instance is first created. After that, it will always remain the same, so you can store it for later use. Access is only possible, when the user instance is active (see the heart_beat column). If it is not, you must first launch the application that uses it (i.e. the web site).