TimeOut Expired. Using Temporary tables

[DotNetJunkies User]

Hi,
I have a temporary table which contains around 2.7 million records. I am iterating through these records in a WHILE Loop and performing certain business rules on these records. Within the loop too i am using a large number of temporary tables.

This entire processing is being done in a stored procedure which is being executed using ADO.NET's ExecuteNonQuery method. The COMMAND object on which this is being executed has its CommandTimeOut property set to 0.

However after around 18 hrs of processing time i get a TimeOut error. This error comes everytime and it takes around 18 hrs each time.

Plz help.

Snapshot of the error log entry is as follows:

*********************************************
Error Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 10
LineNumber: 0
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Number: -2
Procedure: ConnectionRead (WrapperRead()).
Server:
State: 0
Source: .Net SqlClient Data Provider
TargetSite: System.Data.SqlClient.SqlDataReader ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean)
HelpLink: NULL
*******************************************

 

[Miha Markic [MVP C#]]

Hi,

Hmmm, 18hrs.
I don't know where the problem is (perhaps there is a 18hrs limit
somewhere).
However, you might use a sql job object (and schedule it as you want) to
perform the operation instead.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi,
I have a temporary table which contains around 2.7 million records. I am

iterating through these records in a WHILE Loop and performing certain
business rules on these records. Within the loop too i am using a large
number of temporary tables.

This entire processing is being done in a stored procedure which is being

executed using ADO.NET's ExecuteNonQuery method. The COMMAND object on which
this is being executed has its CommandTimeOut property set to 0.

However after around 18 hrs of processing time i get a TimeOut error. This

error comes everytime and it takes around 18 hrs each time.

Plz help.

Snapshot of the error log entry is as follows:

*********************************************
Error Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 10
LineNumber: 0
Message: Timeout expired. The timeout period elapsed prior to completion

of the operation or the server is not responding.

Number: -2
Procedure: ConnectionRead (WrapperRead()).
Server:
State: 0
Source: .Net SqlClient Data Provider
TargetSite: System.Data.SqlClient.SqlDataReader

ExecuteReader(System.Data.CommandBehavior,
System.Data.SqlClient.RunBehavior, Boolean)

HelpLink: NULL
*******************************************

engine supports Post Alerts, Ratings, and Searching.

 

[Peter Vervoorn]

Maybe it is coincidence, but the number of seconds in 18 hours is very close
to System.UInt16.MaxValue.
Could this be because of an overflow somewhere inside ado.net / oleDB?

Hi,
I have a temporary table which contains around 2.7 million records. I am

iterating through these records in a WHILE Loop and performing certain
business rules on these records. Within the loop too i am using a large
number of temporary tables.

This entire processing is being done in a stored procedure which is being

executed using ADO.NET's ExecuteNonQuery method. The COMMAND object on which
this is being executed has its CommandTimeOut property set to 0.

However after around 18 hrs of processing time i get a TimeOut error. This

error comes everytime and it takes around 18 hrs each time.

 

[William \(Bill\) Vaughn]

When you say "iterating", I assume you mean you have a DataReader open and
you're stepping through the rows in the temp table (a #temp of some kind).
or you've created a Dataset with 2.7 million rows. I would not take this
approach (unless you're using an Access/JET database in which case you're
pooched). This kind of processing needs to be done entirely on the server.
You need to write one or more stored procedures to do this work.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi,
I have a temporary table which contains around 2.7 million records. I am

iterating through these records in a WHILE Loop and performing certain
business rules on these records. Within the loop too i am using a large
number of temporary tables.

This entire processing is being done in a stored procedure which is being

executed using ADO.NET's ExecuteNonQuery method. The COMMAND object on which
this is being executed has its CommandTimeOut property set to 0.

However after around 18 hrs of processing time i get a TimeOut error. This

error comes everytime and it takes around 18 hrs each time.

Plz help.

Snapshot of the error log entry is as follows:

*********************************************
Error Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 10
LineNumber: 0
Message: Timeout expired. The timeout period elapsed prior to completion

of the operation or the server is not responding.

Number: -2
Procedure: ConnectionRead (WrapperRead()).
Server:
State: 0
Source: .Net SqlClient Data Provider
TargetSite: System.Data.SqlClient.SqlDataReader

ExecuteReader(System.Data.CommandBehavior,
System.Data.SqlClient.RunBehavior, Boolean)

HelpLink: NULL
*******************************************

engine supports Post Alerts, Ratings, and Searching.