October 30, 2008 at 4:52 am
Hi,
We have an Access front end app that connects to a SQL database. At present it is running on Access 2000 with SQL 2000 but we are upgrading to Access 2007 and SQL 2005.
One of the forms in Access is using an Access query as a dataset. The query is just linking to a view in SQL.
The problem is that the view in SQL is ignoring the ORDER BY clause in the SELECT statement within it.
The view is as follows (I have cut it down to make it simpler)....
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[qryTEST]
AS
SELECT TOP 100 PERCENT
ISNULL(ORDER_REF, CAST(REQ_REF AS CHAR)) AS REQUISITION,
STATUS,
ORDER_REF,
REQ_REF,
SUPP_CODE,
ORDER_DATE
FROM
dbo_PO_ReqHeader
ORDER BY
REQ_REF DESC
GO
When you run the SELECT statement on its own it returns the records based upon the ORDER BY clause. However, when you query the view, i.e. SELECT * FROM qryTEST
it does not use the ORDER BY clause.
Can anyone see anything wrong with the view that would be causing this?
Thanks in advance!
Richard
October 30, 2008 at 5:10 am
Hello,
A couple of questions:-
1) What happens if you try Select * From dbo.qryTEST
I’m just wondering if there is another View of the same name in a different Schema?
2) Are you trying the Select from SSMS or via Access?
If it’s via Access then may be that is re-sorting the data.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
October 30, 2008 at 5:29 am
John Marsh (10/30/2008)
1) What happens if you try Select * From dbo.qryTEST
Same result - I created this for testing this issue - the real view contains more columns and joins but both have the same problem.
John Marsh (10/30/2008)
2) Are you trying the Select from SSMS or via Access? If it’s via Access then may be that is re-sorting the data.
I'm querying it in SQL. In Access there is an ORDER BY clause but I am trying to get it working in SQL so I can then remove the ordering in ACCESS. Easier for me to troublshoot that way.
October 30, 2008 at 5:41 am
You are correct, an order by in a view or subquery is ignored. Order by only applies if it's in the outer select statement, unless there is a row-limiting top clause specified. This is intended behaviour and is by design.
Put the order by clause in the outer-most query, ie the one in Access.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 5:44 am
OK, have done some further tests:
1. created new table:
CREATE TABLE TEST
REF INT NOT NULL,
DATE DATETIME NOT NULL
2. Inserted 4 rows - made sure that the REF was sequential but the dates were not. E.G. date for 3 was more recent that date for 4.
3. Created a view
CREATE VIEW VTEST
AS
SELECT TOP 100 PERCENT * FROM VTEST
ORDER BY DATE DESC
4. Selected from view and results were not ordered by date descending.
October 30, 2008 at 5:56 am
GilaMonster (10/30/2008)
You are correct, an order by in a view or subquery is ignored. Order by only applies if it's in the outer select statement, unless there is a row-limiting top clause specified. This is intended behaviour and is by design.Put the order by clause in the outer-most query, ie the one in Access.
OK, removed the ORDER BY clause on the view in SQL.
There is then the option to order the Query in Access....I've not done that.
I have however added an order on the form which displays the results of the query as I assume that is the outer-most query. Still doesn't work though 🙁
Guess I'm treading on Access ground now.....if anyone has experience then please let me know if you have any ideas, otherwise do not worry!
It must be related to Access 2007 as this was working perfectly in Access 2000.
October 30, 2008 at 8:36 am
richard (10/30/2008)
OK, have done some further tests:1. created new table:
CREATE TABLE TEST
REF INT NOT NULL,
DATE DATETIME NOT NULL
2. Inserted 4 rows - made sure that the REF was sequential but the dates were not. E.G. date for 3 was more recent that date for 4.
3. Created a view
CREATE VIEW VTEST
AS
SELECT TOP 100 PERCENT * FROM VTEST
ORDER BY DATE DESC
4. Selected from view and results were not ordered by date descending.
Regarding number 4, true, the ORDER BY is iqnored when using the TOP 100 PERCENT. This is not limiting the number of rows returned by the query.
October 30, 2008 at 8:48 am
richard (10/30/2008)
There is then the option to order the Query in Access....I've not done that.I have however added an order on the form which displays the results of the query as I assume that is the outer-most query. Still doesn't work though 🙁
Guess I'm treading on Access ground now.....if anyone has experience then please let me know if you have any ideas, otherwise do not worry!
It must be related to Access 2007 as this was working perfectly in Access 2000.
I've worked with Access front ends to SQL Server before. What I'd recommend is to put the ORDER BY in your Access Query, but make sure that your query is a pass-through query, especially if you are using a regular Access application instead of an Access Project (.adp). An Access Project works simmilar to a pass-through query so those shouldn't be a problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply