I am working on a database with a front end in Access 97 and a back end in SQL 6.5. I ran into a problem this weekend using a stored procedure and a pass-through query.

The stored procedure is designed to help fill orders. It selects the next order to be filled based on a sort, then updates the order as filled as long as the timestamp value is still the same (ie, as long as no-one else has filled the order in the meantime). If the update is successful, the procedure returns the Order ID so that an order form can be printed. If the update is not successful, the procedure returns zero, so that the user can try again.

This all works beautifully in SQL Server, but when I try to run the same procedure as a pass-through query in Access (either in code or as an actual pass-through query definition in the database window) it does not update and always returns zero. Any ideas on why this occurs?

Code is below. Any help or ideas would be much appreciated!

CREATE PROCEDURE usp_FillOrder
@Employee int,
@FillFax bit AS

DECLARE @OrderID int
DECLARE @LastChanged timestamp
DECLARE @Rows int
DECLARE @Return int

SET ROWCOUNT 1
SELECT @OrderID = OrderID, @LastChanged = LastChanged FROM SupplyOrders
JOIN ShippingTypes ON SupplyOrders.ShippingTypeID = ShippingTypes.ShippingTypeID
WHERE SupplyOrders.Submitted Is Not Null And SupplyOrders.DateFilled Is Null AND SupplyOrders.Returned IS NULL AND OrderID NOT IN (SELECT OrderID FROM OrdersToBeApproved)
ORDER BY ShippingTypes.Priority, Submitted

IF @OrderID IS NULL
BEGIN
SELECT @Return = -1
END
ELSE

BEGIN
SET NoCount On
UPDATE SupplyOrders SET DateFilled = GetDate(), FilledBy = @Employee
WHERE OrderID = @OrderID AND LastChanged = @LastChanged
SELECT @Rows = @@RowCount

IF @Rows > 0
BEGIN
SELECT @Return = @OrderID
END
ELSE
BEGIN

SELECT @Return = 0
END
END

SELECT @Return AS OrderID