Sql 2005 with Access 2003 sort order issue

[Fredrick A. Zilz]

I have an access 2003 adp using a SQL 2005 database as the datasource. I
have a list which uses a function as the data source. When I execute the
function the results are sorted per the order by fields, but when I look at
the list in Access the list is seemingly random? Any ideas as to why?

SQL Function:
ALTER FUNCTION [dbo].[QtyByOrderNo_PendingOrders]()

RETURNS TABLE

AS

RETURN ( SELECT TOP 100 PERCENT CustomerOrders.IDCUST,
CustomerOrders.NAMECUST, dbo.Tbl_IH_LotNo.LotID, dbo.Tbl_IH_LotNo.MaLotNo,

dbo.Tbl_Inventory.OrderID, dbo.Tbl_IH_LotNo.OrderCode,
SUM(dbo.Tbl_Inventory.Qty) AS QtyOrdered, dbo.Tbl_Inventory.ORDUNIQ,

dbo.Tbl_Inventory.LINENUM, CustomerOrders.ORDDATE, CustomerOrders.EXPDATE,
CustomerOrders.ORIGQTY

FROM dbo.Tbl_Drums INNER JOIN

dbo.Tbl_SubLots ON dbo.Tbl_Drums.SubLotID = dbo.Tbl_SubLots.SubLotID INNER
JOIN

dbo.Tbl_IH_LotNo ON dbo.Tbl_SubLots.LotID = dbo.Tbl_IH_LotNo.LotID INNER
JOIN

dbo.Tbl_Inventory ON dbo.Tbl_Drums.DrumID = dbo.Tbl_Inventory.DrumID INNER
JOIN

dbo.CustomerOrders() CustomerOrders ON dbo.Tbl_Inventory.ORDUNIQ =
CustomerOrders.ORDUNIQ AND

dbo.Tbl_Inventory.LINENUM = CustomerOrders.LINENUM

WHERE (dbo.Tbl_Inventory.[Shipped Date] IS NULL)

GROUP BY dbo.Tbl_IH_LotNo.LotID, dbo.Tbl_IH_LotNo.MaLotNo,
dbo.Tbl_IH_LotNo.OrderCode, dbo.Tbl_Inventory.ORDUNIQ,
dbo.Tbl_Inventory.LINENUM,

dbo.Tbl_Inventory.OrderID, CustomerOrders.NAMECUST, CustomerOrders.IDCUST,
CustomerOrders.ORDDATE, CustomerOrders.EXPDATE,

CustomerOrders.ORIGQTY

HAVING (NOT (dbo.Tbl_Inventory.LINENUM IS NULL))

ORDER BY CustomerOrders.NAMECUST, dbo.Tbl_Inventory.OrderID )



What would make the Access 2003 list sort differently than the datasource?

 

[Sylvain Lafontaine]

The key word here is probably 2005.

It's a common misconception that Order By in Functions and Views should be
followed by SQL-Server; especially when you add a TOP 100 PERCENT clause.

The TOP clause is usually used in conjonction with an Order By to retrieve a
particular subset; however when you set it to 100%, it's pretty useless and
SQL-Server 2005 - beeing more optimized than its 2000 version counterpart -
simply drop it.

This is not an ADP problem here but a difference between SQL2000 and 2005;
the 2005 version beeing more in line with the published standard for the SQL
language.

You will have to enclose your call to the function inside a Select statement
with an Order By clause (the Order By clause on the Select statement and not
inside the function (or a view)).

 

[Fredrick A. Zilz]

Thank you, Thank you.
You resolved my issue and increased my SQL knowledge.

The key word here is probably 2005.

It's a common misconception that Order By in Functions and Views should be
followed by SQL-Server; especially when you add a TOP 100 PERCENT clause.

The TOP clause is usually used in conjonction with an Order By to retrieve
a particular subset; however when you set it to 100%, it's pretty useless
and SQL-Server 2005 - beeing more optimized than its 2000 version
counterpart - simply drop it.

This is not an ADP problem here but a difference between SQL2000 and 2005;
the 2005 version beeing more in line with the published standard for the
SQL language.

You will have to enclose your call to the function inside a Select
statement with an Order By clause (the Order By clause on the Select
statement and not inside the function (or a view)).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have an access 2003 adp using a SQL 2005 database as the datasource. I
have a list which uses a function as the data source. When I execute the
function the results are sorted per the order by fields, but when I look
at the list in Access the list is seemingly random? Any ideas as to why?

SQL Function:
ALTER FUNCTION [dbo].[QtyByOrderNo_PendingOrders]()

RETURNS TABLE

AS

RETURN ( SELECT TOP 100 PERCENT CustomerOrders.IDCUST,
CustomerOrders.NAMECUST, dbo.Tbl_IH_LotNo.LotID,
dbo.Tbl_IH_LotNo.MaLotNo,

dbo.Tbl_Inventory.OrderID, dbo.Tbl_IH_LotNo.OrderCode,
SUM(dbo.Tbl_Inventory.Qty) AS QtyOrdered, dbo.Tbl_Inventory.ORDUNIQ,

dbo.Tbl_Inventory.LINENUM, CustomerOrders.ORDDATE,
CustomerOrders.EXPDATE, CustomerOrders.ORIGQTY

FROM dbo.Tbl_Drums INNER JOIN

dbo.Tbl_SubLots ON dbo.Tbl_Drums.SubLotID = dbo.Tbl_SubLots.SubLotID
INNER JOIN

dbo.Tbl_IH_LotNo ON dbo.Tbl_SubLots.LotID = dbo.Tbl_IH_LotNo.LotID INNER
JOIN

dbo.Tbl_Inventory ON dbo.Tbl_Drums.DrumID = dbo.Tbl_Inventory.DrumID
INNER JOIN

dbo.CustomerOrders() CustomerOrders ON dbo.Tbl_Inventory.ORDUNIQ =
CustomerOrders.ORDUNIQ AND

dbo.Tbl_Inventory.LINENUM = CustomerOrders.LINENUM

WHERE (dbo.Tbl_Inventory.[Shipped Date] IS NULL)

GROUP BY dbo.Tbl_IH_LotNo.LotID, dbo.Tbl_IH_LotNo.MaLotNo,
dbo.Tbl_IH_LotNo.OrderCode, dbo.Tbl_Inventory.ORDUNIQ,
dbo.Tbl_Inventory.LINENUM,

dbo.Tbl_Inventory.OrderID, CustomerOrders.NAMECUST,
CustomerOrders.IDCUST, CustomerOrders.ORDDATE, CustomerOrders.EXPDATE,

CustomerOrders.ORIGQTY

HAVING (NOT (dbo.Tbl_Inventory.LINENUM IS NULL))

ORDER BY CustomerOrders.NAMECUST, dbo.Tbl_Inventory.OrderID )



What would make the Access 2003 list sort differently than the
datasource?