Sorting from Views in SQL 2005 using an ADP

Question

Sorting from Views in SQL 2005 using an ADP

Perry Harts

Ten Centuries

Points: 1271
More actions
August 21, 2007 at 11:07 am

#69681

I have a SQL 2005 database connected with an Access 2003 ADP. None of the views sort properly in the ADP. All of the views have TOP specified. The problems occurs in views with joins and even if the view has only one table. Does this problem occur on other ADP connections to SQL 2005? Please let me know. Is there a resolution?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

select top 100 percent ... ORDER BY ..., SQL Server 2005 ignores the order by clause. This is actually how it should be handled. There are several discuss threads on this site that cover the issue.

You should put your order by in the select that is using the view, not in the view itself.

I am sure others will jump in here either with links or additional thoughts, suggestions, opinions, etc.

Perry Harts Ten Centuries Points: 1271 More actions · Answer 2

Lynn,

Thanks for directing me to previous posts, where I found a solution posted on 9/15/2006 by you. The solution was to change the select to less than TOP 100%. I found 99.999999% works well. Now I don’t have to rewrite the ADP. Thanks for your help today and last year.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

It really is only a work around. If your table gets big enough, it will stop working. A view, returning all rows is treated as a table, the order of the records is not guaranteed. The order by should be in the select statement where the view is referenced, not in the view.

jfmccabe SSCrazy Points: 2547 More actions · Answer 4

Have a look at http://msdn2.microsoft.com/en-us/library/bb188204.aspx

Microsoft SQL Server 9.0 Technical Articles

Optimizing Microsoft Office Access Applications Linked to SQL Server

The word article that you get clearly states that:

"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."

Perry Harts Ten Centuries Points: 1271 More actions · Answer 5

Thanks for the link. Upon further test in my testing environment I did get unsatisfactory results.