Change 'timeout' value for large table queries??

[JennyPeters]

I have a large archive table, 200,000 records, and when trying to append record from this archive table to another empty table, I get a message saying 'Timeout expired', and my SP stops.

Is there a way to change the 'Timeout' value, so this query has time to actually run?

Thanks,

Jenny

 

[ClaireHCM]

You could try to play around with

sp_configure
Parameter :query wait

 

[SQLSister]

An append with a table that small should not be timing out. You need to post your code so we can see what the problem is. You aren;t by chance using cursors are you?

 

[JennyPeters]

Thanks, but I'm not sure what that means.

Any further direction you can give me?

Jenny

 

[JennyPeters]

Here's the code...

It's a huge SP with many table interactions. Should I break some of them down into views, and combine them?

ALTER PROCEDURE dbo.spShippingReport_DataFrom_TblProdShippedArchive_orig
AS INSERT INTO dbo.tblShippingReport_AllData
(ShipmentNo, ShipID, CompanyName, fkCompanyID, InvoiceNo, InvID, WorkOrder, PartNumber, PartID, PiecesPerBox, [Total Bottles], WorkOrderSeq,
Shipdate, NoBoxes)
SELECT dbo.tblShipping.ShipmentNo, dbo.tblShipping.ShipID, dbo.tblCustomers1.CompanyName, dbo.tblShipping.fkCustomerID AS CustomerID,
dbo.tblInvoice.InvoiceNo, dbo.tblInvoice.InvID, dbo.vwShippingReport_ShippedArchivedBottles.WorkOrder, dbo.tblPart.PartNumber, dbo.tblPart.PartID,
dbo.tblPart.PiecesPerBox, dbo.tblPart.PiecesPerBox * dbo.tblShipping.NoBoxes AS [Total Bottles], dbo.tblShipping.WorkOrderSeq,
CONVERT(varchar(10), dbo.tblShipping.ShipDate, 101) AS Shipdate, dbo.tblShipping.NoBoxes
FROM dbo.tblCustomers1 RIGHT OUTER JOIN
dbo.vwShippingReport_ShippedArchivedBottles LEFT OUTER JOIN
dbo.tblShipping ON dbo.vwShippingReport_ShippedArchivedBottles.WorkOrder = dbo.tblShipping.WorkOrder LEFT OUTER JOIN
dbo.tblOrders ON dbo.vwShippingReport_ShippedArchivedBottles.WorkOrder = dbo.tblOrders.WorkOrderNo LEFT OUTER JOIN
dbo.tblPart ON dbo.tblOrders.PartID = dbo.tblPart.PartID LEFT OUTER JOIN
dbo.tblInvoice ON dbo.tblShipping.ShipID = dbo.tblInvoice.fkShipID ON dbo.tblCustomers1.CustomerID = dbo.tblShipping.fkCustomerID
WHERE (dbo.vwShippingReport_ShippedArchivedBottles.Location = - 888)
GROUP BY dbo.vwShippingReport_ShippedArchivedBottles.WorkOrder, dbo.tblShipping.ShipmentNo, dbo.tblShipping.fkCustomerID,
dbo.tblCustomers1.CompanyName, dbo.tblShipping.ShipID, dbo.tblInvoice.InvID, dbo.tblInvoice.InvoiceNo, dbo.tblPart.PartNumber, dbo.tblPart.PartID,
dbo.tblPart.PiecesPerBox, dbo.tblShipping.WorkOrderSeq, CONVERT(varchar(10), dbo.tblShipping.ShipDate, 101), dbo.tblShipping.NoBoxes,
dbo.tblPart.PiecesPerBox * dbo.tblShipping.NoBoxes

 

[SQLSister]

What kind of indexing do you have on the table?