Posted by RickW on 03/06/06 19:40

We've hit this Order By problem as well. I don't mind exactly that it
changed from SQL Server 2000. I am a bit torqued that at MSDN the
article Behavior Changes to Database Engine Features in SQL Server 2005
(http://msdn2.microsoft.com/en-us/library/ms143359.aspx) doesn't say
anything remotely like, "This used to work but this way, but not
anymore." I'm also a bit miffed that in the conversion process nothing
says to you (according to our DBA) "Oh, those Order By clauses? Forget
about them."

I would be only too happy for someone to point to the document that
would have alerted us (without having to read Books Online end-to-end)
to this problem. And yes, it is a problem.

Here's the thing I really don't get: If by definition (as always) and
now in fact (with SS2005) you simply cannot return an ordered set from a
view, how exactly are you supposed to cope with that in an application?

I'm using Access, looking at data via linked tables with views as their
sources. All of a sudden, these don't return ordered sets. Fine, I'll
just go through and rewite all my views as stored procedures or
functions.

(By the way, don't anybody utter a word about SQL Server being "within
its rights", or any other such theoretical rubbish. It's a tool. It
has no rights. It is convenient, or it is not. Right now, it is not.)

Oh, wait. I forgot! I can't link to functions or stored procedures
from Access. (Another 'by the way': No prissy "oooohh, why are you
using Access?" nonsense either.)

Hmmmmmmm...

We even tried setting compatibility back a version, and still no joy.

I would love nothing more than to be told that I've missed a good
workaround or that we goofed in the compatibility setting or that
somewhere here I have just boneheadedly overlooked a simple way around
all of this. Modifying all of my view-based data-consuming objects in
the application does not appeal to me, but it's looking inevitable right
now.

Another thought: We were about half way to converting all of our local
Access queries to views. Maybe that's not such a great idea after all.
At the moment, it looks like to only way I'm going to get an ordered set
out of SQL Server is with a local query or a report (same thing under
the hood).

*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]