Sim1980
asked on
Query timeout expired message when running stored procedure in Access 2003 ade file
Hi all.
I have an Access 2003 ade file that runs a stored procedure (the ade file's backend is SQL Server 2000) and recently we began getting the following message: "Query timeout expired".
Now, the table has grown a lot.
Below is the stored procedure. Any ideas what I can do to have the stored procedure run and not have that message pop up? Thank you in advance!
I have an Access 2003 ade file that runs a stored procedure (the ade file's backend is SQL Server 2000) and recently we began getting the following message: "Query timeout expired".
Now, the table has grown a lot.
Below is the stored procedure. Any ideas what I can do to have the stored procedure run and not have that message pop up? Thank you in advance!
ALTER PROCEDURE InventoryOH
( @PeriodName varchar(18)
)
AS
SELECT OrderID, ProjectManager, CustomerName, Community, Lot, Block, TotalCosts, TotalBilling, ReturnDate, WorkType, ProductType, Warehouse,
(SELECT begindate FROM periods WHERE name = @PeriodName) AS begindate,
(SELECT enddate FROM periods WHERE name = @PeriodName) AS enddate,
(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS ReceiveDate,
(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS IssueDate,
(SELECT SUM(Price) FROM WorkOrderItems WHERE ItemType = '1' AND WorkOrderID = OrderID) AS TotalMaterial,
(SELECT SUM(Price) FROM WorkOrderItems WHERE ItemType = '2' AND WorkOrderID = OrderID) AS TotalLabor
FROM WorkOrders
WHERE OrderID IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
OrderID NOT IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
Void = 0 AND
OrderID NOT IN (SELECT OrderID FROM WorkOrders WHERE ReturnDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1)
ORDER BY OrderID
RETURN
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nevermind my previous post. I figured it out:
I will try your suggestions and report back.
SELECT OrderID, ProjectManager, CustomerName, Community, Lot, Block, TotalCosts, TotalBilling, ReturnDate, WorkType, ProductType, Warehouse,CASE WHEN ItemType = '1' THEN SUM(Price) ELSE 0 END AS TotalMaterial,
CASE WHEN ItemType = '2' THEN SUM(Price) ELSE 0 END AS TotalLabor,
(SELECT begindate FROM periods WHERE name = @PeriodName) AS begindate,
(SELECT enddate FROM periods WHERE name = @PeriodName) AS enddate,
(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS ReceiveDate,
(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS IssueDate
FROM WorkOrders INNER JOIN
WorkOrderItems ON WorkOrders.OrderID = WorkOrderItems.WorkOrderID
WHERE OrderID IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1 ) AND
OrderID NOT IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
Void = 0 AND
OrderID NOT IN (SELECT OrderID FROM WorkOrders WHERE ReturnDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1)
Group By WorkOrders.OrderID, WorkOrders.ProjectManager, WorkOrders.CustomerName, WorkOrders.Community, WorkOrders.Lot, WorkOrders.Block, WorkOrders.TotalCosts, WorkOrders.TotalBilling,
WorkOrders.ReturnDate, WorkOrders.WorkType, WorkOrders.ProductType, WorkOrders.Warehouse,ItemType
ORDER BY WorkOrders.OrderID
I will try your suggestions and report back.
ASKER
If you compare to my initial post you'll see the addition of the Case statements and the removal of the Sum(price) from the subqueries in my initial post.
Open in new window