SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Learning...
    Join Date
    Jan 2003
    Posts
    781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question time out problem

    I am using sql server 2000. From the asp page i am calling a stored procedure that does all the processing that includes:

    1. create the query dynemically according to the selections made by the user.
    2. run query
    3. if records found, create a comma delimited list
    4. pust that file on a file server, from where the user can then download the file

    it return -1 if an error has accoured, 0 if no records are found, or file id if the records are found and file is written and moved to its location successfully.

    i have increased the script timeout time to 800
    Code:
     Server.ScriptTimeout = 800;
    and connection time out to 800 also
    Code:
     Conn.CommandTimeout = 800;
    here is the stored procedure call
    Code:
     var Conn = Server.CreateObject("ADODB.Connection"); 
    Conn.CursorLocation = 3 //adUseClient;
    Conn.Open(strConnection);
     
    Conn.CommandTimeout = 800;
     
    //Now passing the values to the stored procedure to store these
     
    cmd = Server.CreateObject("ADODB.Command");
    cmd.CommandText = "usp_Process";
    cmd.CommandType = 4; // Stored procedure
    var p = cmd.Parameters;
     
    var idin = cmd.CreateParameter("@ExportID", 131, 1);
    idin.NumericScale = 0;
    idin.Precision = 10;
    p.Append(idin);
     
    var idout = cmd.CreateParameter("@XferID", 131, 2);
    idout.NumericScale = 0;
    idout.Precision = 9;
    p.Append(idout);
     
    var zip = cmd.CreateParameter("@Zip", 131, 1);
    zip.NumericScale = 0;
    zip.Precision = 9;
    p.Append(zip);
     
    var debug = cmd.CreateParameter("@Debug", 131, 1);
    debug.NumericScale = 0;
    debug.Precision = 9;
    p.Append(debug);
     
    cmd("@ExportID") = 0 + nIdIn;
    cmd("@Zip") = 0;
    cmd("@Debug") = 0;
     
    cmd.ActiveConnection = Conn;
    cmd.Execute(); 
    strId = cmd("@XferID")
     
    Conn.CommandTimeout = 60; 
    Conn.Close();
     
    return strId;
    here is the error that i am getting:
    Code:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
    [Microsoft][ODBC SQL Server Driver]Timeout expired
     
    and the error is on this line
    cmd.Execute();


    is there some thing that i am doing wrong here? Running this from the query analyzer takes about 3 minutes for a very complex query. Our users know about this so time is not a problem.

  2. #2
    SitePoint Enthusiast Nawaz Ijaz's Avatar
    Join Date
    Feb 2005
    Location
    Lahore, Pakistan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Running this from the query analyzer takes about 3 minutes for a very complex query
    If Query Analyzer takes 3 minutes then server can't handle that. If you are fetcing some data, then go for some paging technique or restrict the fetched data.
    Nawaz Ijaz [Lahore, Pakistan]
    Boy: He is big..i wont like to fight him...
    Achilles: Thatz Y no one wud remember u after thousand years...!!

  3. #3
    Learning...
    Join Date
    Jan 2003
    Posts
    781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have set the

    Conn.ConnectionTimeout = 0;
    Conn.CommandTimeout = 0;
    still i am getting the time out error.

  4. #4
    Learning...
    Join Date
    Jan 2003
    Posts
    781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    problem is solved. i have to set the command time out for the command obejct. Connection's connection time out and command time out are not that important in my case.

    Code:
     cmd = Server.CreateObject("ADODB.Command"); 
    cmd.CommandTimeout = 600;
    cmd.CommandText = "usp_Process";
    cmd.CommandType = 4; // Stored procedure
    var p = cmd.Parameters;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •