When testing our app against SQL 2005, I ran into a SqlException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. that doesn't occur when the query is run against Sql 2000.

Here's snips from the dynamic list code that generated it:

        With fields
            .DefineField(EmployeeFieldIndex.IDEmployee, 0, "IDEmployee")
            .DefineField(EmployeeFieldIndex.FName, 1, "Name")
            .DefineField(EmployeeFieldIndex.FName, 2, "FName")
            .DefineField(EmployeeFieldIndex.FName, 3, "LName")
            Dim fNameExpression As New Expression(FactoryClasses.EntityFieldFactory.Create(EmployeeFieldIndex.FName), ExOp.Add, " ")
            Dim nameExpression = New Expression(fNameExpression, ExOp.Add, FactoryClasses.EntityFieldFactory.Create(EmployeeFieldIndex.LName))
            fields("Name").ExpressionToApply = nameExpression
        End With

        sortBy.Add(FactoryClasses.SortClauseFactory.Create(EmployeeFieldIndex.FName, SortOperator.Ascending))
        sortBy.Add(FactoryClasses.SortClauseFactory.Create(EmployeeFieldIndex.LName, SortOperator.Ascending))

and here's the generated Sql:

SELECT DISTINCT 
    [dbo].[Employee].[IDEmployee], 
    ([dbo].[Employee].[FName] + ' ') + [dbo].[Employee].[LName] AS [Name], 
    [dbo].[Employee].[FName], 
    [dbo].[Employee].[FName] AS [LName] 
FROM 
    [dbo].[Employee]  
ORDER BY 
    [dbo].[Employee].[FName] ASC,
    [dbo].[Employee].[LName] ASC

You'll notice the typo created by a member of my team: Employee.FName is listed twice, once with the alias "LName". Obviously, she meant to enter Employee.LName instead.

This would ordinarily cause the exception given above even in Sql 2000, except that apparently Sql 2000 has what I would call a little bug. Or, maybe it's a feature.

The interesting thing is that Sql2000 didn't complain and used the aliased "LName" from the SELECT clause in the ORDER BY clause, even though the column specified in the ORDER BY clause is clearly the full qualified column dbo.Employee.LName, which is not in the SELECT list. I would say it was Sql 2000 that screwed up and Sql 2005 fixed the error, but, nevertheless it's something to watch out for in your code.

Jeff...

jeffreygg wrote:

The interesting thing is that Sql2000 didn't complain and used the aliased "LName" from the SELECT clause in the ORDER BY clause, even though the column specified in the ORDER BY clause is clearly the full qualified column dbo.Employee.LName, which is not in the SELECT list. I would say it was Sql 2000 that screwed up and Sql 2005 fixed the error, but, nevertheless it's something to watch out for in your code.

I don't think sqlserver 2000 uses the aliased column LName, I think it uses the table column LName, simply because it is written out in full.

would it have been: ORDER BY FName ASC, LName DESC

then it would have been debatable. Or does it use the aliased column? (i.e.: are the LName values unsorted?)

The exception is thrown in 2005 I think because as you said, sqlserver 2000 has a little issue (I think) with this that it should report an error but 'forgottaboutit'