2003 ADP connected to SQL Server 2005 - lost sort order

[GA]

Hello all,

I wonder if anybody has come across the same issue as the one I have or at
least knows a way to resolve it.

I had a dsn-less Access 2003 .mde running from each local machine and
connected to SQL Server 2005. Everything worked as expected but then users
started complaining about performance issues and I confirmed this with
some queries taking several minutes to run.

I've moved to an .ade and everything is running at a more acceptable pace
now *but* there is one issue that I cannot resolve. My main form is
populated by a view that pulls information together on the server. The
view has a SORT BY line and appears correctly sorted in SQL Server
Management Studio Express. When displayed in my front end it is not sorted.

I've read around this and I'm aware that ORDER BY works in conjunction
with SELECT TOP 100 and does guarantee a sorted result being output.

I'm out of ideas but if anybody has resolved this in some way I'd
appreciate any pointers.

Thanks - GA

 

[George Avery]

I just re-read my post and notice that I wrote "and does" when what I
meant to write was "but does not" i.e. the output is not guaranteed to
be alphabetically sorted.

GA

 

[Douglas J Steele]

Not sure why you're saying it doesn't guaranteed to be alphabetically
sorted. If you've got an ORDER BY, that's the order in which it'll be
sorted!

What's the actual SQL of your query?

"George Avery" wrote in message

I just re-read my post and notice that I wrote "and does" when what I
meant to write was "but does not" i.e. the output is not guaranteed to
be alphabetically sorted.

GA

 

[George Avery]

The truth is that the performance improvement that I've achieved makes
this something of a non-issue but it irritates me nevertheless.

I've finished work for the day now but I have found :-

http://msdn.microsoft.com/en-us/library/bb188204.aspx


"In SQL Server 2000, view results were reliably returned in the order
specified in TOP 100 PERCENT queries. In SQL Server 2005 this behavior
has changed. ORDER BY is still supported in views that use TOP 100
PERCENT, but the rows are not returned in that order. There is a new
workaround: Instead of using TOP 100 PERCENT, you can use TOP 99999999
with any number greater than or equal to the number of rows in the
result set. However, this technique is unsupported and could prove as
unreliable in the next version as the old workaround did. In addition,
adding this sort can hurt performance if the view is joined to other
data in a query. So stick with selecting from the view and adding an
ORDER BY clause to create a sort order, just as you do with linked
tables."

So I'll be trying out the workaround tomorrow.

GA

 

[George Avery]

Just a follow up in case it is of interest to anyone.

Changing TOP 100 PERCENT to TOP (999) worked perfectly for me.

There is the potential for things to go wrong if the number of records
ever exceeds the hard coded number but in my case this is not going to
happen so it's fine for my purposes.

GA