This is kind of a strange one. I'm moving our code to SQL Server 2008 from 2000.
All works as expected on SQL Server 2000 and most everything works in 2008. Except for this one Stored Procedure. In 2008 I am getting:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Fine. But I'm not using Order By anywhere in the Stored procedure.
I am using it in one function that the SP uses. However when I run that function in 08 it executes successfully.
I am using ALIASED columns and SELECT DISTINCT but my understanding is that this is only a problem if you use aliased columns in your order by clause, which this SP isn't using.
So maybe its coming from my calling of the SP from asp.net. not sure if this board can assist me with that part but I'll include it here.
The results of my sp are returned to the app and put into a DataView which is then bound to a gridview. I have sorted columns in the grid which I use like:
dv.Sort = strSortColumn
where dv is my dataview and strSortColumn is the aliased column.
The sorting is happening in the Dataview and not on the database so I wouldn't think there would be an issue there.