Re: TimeOut Expired. Using Temporary tables

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 07/30/04 

Date: Fri, 30 Jul 2004 11:04:55 -0700

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.
__________________________________
"DotNetJunkies User" <User@-NOSPAM-DotNetJunkies.com> wrote in message
news:uWW2lOfdEHA.2664@TK2MSFTNGP09.phx.gbl...
> 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
> *******************************************
>
> ---
> Posted using Wimdows.net NntpNews Component -
>
> Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup
engine supports Post Alerts, Ratings, and Searching.

Relevant Pages

  • Re: End function if it Takes too long
    ... it will timeout if the response doesn't come quick enought... ... address formatted like the post office likes, again xml. ... on this part of the app. ... temp by ZIP for where the solar array is installed. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ADO.NET Timeout Exception - I have tried everything
    ... Its a complex stored proc with the end result as an insert to a temp ... Then I do a select from the temp table - in the store proc. ... The timeout period elapsed prior to completion of the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Cant understand this.
    ... I'm not sure what this piece of code does: Infact it is getting a ... database query and iterating ... ... my %temp; ...
    (perl.beginners)