SQL Server 2005 ADP Sorting

Question

SQL Server 2005 ADP Sorting

Perry Harts

Ten Centuries

Points: 1271
More actions
March 12, 2007 at 10:18 am

#171623

I have views set up to sort in SQL Server 2005. I am connect with an Access ADP. From Access none of the views are sorted as specified by the views. I do not have this issue with SQL 2000.

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi,

SQL Sever Views can not be sorted (include ORDER BY) unles TOP is specified too. It was the same way in 2000 too. Please, give an example of what you are doing.

Regards,Yelena Varsha

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

Hi Yelena,

The SQL view is:

SELECT TOP (100) PERCENT dbo.DirectoryRights.DirectoryListID, dbo.DirectoryList.FileName

FROM dbo.DirectoryList INNER JOIN

dbo.DirectoryRights ON dbo.DirectoryList.FileID = dbo.DirectoryRights.DirectoryListID

ORDER BY dbo.DirectoryList.FileName

When executed from the Management Studio the FileName is sorted correctly.

When executed from an Access adp the FileName is not sorted correctly.

Thanks for you attention.

Perry

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Linked tables return the sorted view, I tested. How do you use ADP? are you talkig about ADP functions? We found out that in Access 2003 ADP functions don't really work comparing how they worked in Access 2000, and we got an confirmation that they are not supposed to work.

Regards,Yelena Varsha

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

I am not using functions. I am using a view.

In my access project I go to queries where I can open views. In SQL Server 2000 the sort for those views were correct. When connect to SQL Server 2005 the sort does not.

Thanks

Perry