SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    classic asp throwing time out error

    Hi ,
    can some one please help me on this.

    My sql procedure to compare 2 comma seperated rows from consecutive rows is running fine when I execute from database.After passing parameters from front end it is throwing time out error.Not sure why this is happening...
    here is my procedure:



    alter Proc [dbo].[GetLatestPhasesEditHistory]
    @ProjectID int
    -- [GetLatestPhasesEditHistory] 216
    As Begin

    Declare @new varchar(1000),@old varchar(1000),@return varchar(1000), @i int, @tblCnt int
    ,@UpdatedDate datetime,@UpdatedBy varchar(10),@Phase varchar(1000)


    If object_id('dbo.#t') is not null Begin ; drop table dbo.#t ;End
    CREATE TABLE dbo.#t(Phase varchar(1000),UpdatedDate datetime,UpdatedBy varchar(10));
    --CREATE INDEX IDX_phase ON #t(Id)

    if (select COUNT(*) from phase WHERE PROJECT_ID=@ProjectID ) > 0
    BEGIN
    Set @tblCnt = (select COUNT(*) from phase WHERE PROJECT_ID=@ProjectID )
    Set @i=1
    While @i != (@tblCnt) Begin
    set @new= (Select top 1 phase from (select Top (@i) phase,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
    set @old=(Select top 1 phase from (select Top (@i + 1) phase,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
    set @UpdatedDate=(select top 1 UpdatedDate from (select Top (@i)UpdatedDate,ID from phase where project_id=@ProjectID order by ID desc)a order by ID asc)
    set @UpdatedBy= (select top 1 UpdatedBy from (Select top(@i) UpdatedBy,ID from phase where project_id=@ProjectID order by ID desc)a order by ID asc)
    INSERT INTO #t (Phase)
    exec [dbo].[PMT_GetPhasesEditHistory] @strN = @new,@strO = @old
    --Update #t set UpdatedBy = @UpdatedBy, UpdatedDate=@UpdatedDate where Id = (select max(ID) from #t)
    Set @i = @i +1
    End
    set @old = (Select top 1 phase from (select Top (@tblCnt) phase,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
    set @UpdatedDate=(Select top 1 UpdatedDate from (select Top (@tblCnt) UpdatedDate,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
    set @UpdatedBy= (Select top 1 UpdatedBy from (select Top (@tblCnt) UpdatedBy,ID from Phase where project_id=@ProjectID order by ID desc) a order by ID asc)
    INSERT INTO #t (Phase,UpdatedDate,UpdatedBy) Values (@old,@UpdatedDate,@UpdatedBy)
    END
    select Phase,UpdatedDate,UpdatedBy from #t
    drop table #t;
    End

    ---------------------------------------------------------------------------------

    My asp code for this is :

    <%

    Set objConnPhase=Server.CreateObject("ADODB.Connection ")
    objConnPhase.CommandTimeout = 120
    Set objRSPhase=Server.CreateObject("ADODB.Recordset")
    Set ObjcmdPhase=Server.CreateObject("ADODB.Command")
    objConnPhase.Open Application("conn")
    Set ObjcmdPhase.ActiveConnection = objConnPhase
    ObjcmdPhase.CommandText = "GetLatestPhasesEditHistory"
    ObjcmdPhase.CommandType = adCmdStoredProc
    ObjcmdPhase.Parameters.Append ObjcmdPhase.CreateParameter("@ProjectID", adInteger, adParamInput, 4, strProjectId)
    set objRSPhase= ObjcmdPhase.Execute

    %>
    <%If Not (objRSPhase.BOF and objRSPhase.EOF) Then%>

    <table id="tblphase" cellspacing="0" cellpadding="0" border="0" class="gridlines">
    <tr class="grdHeading" align="left" style="font-weight:bold;white-space:nowrap;">
    <%For intCol = 0 To 3%>
    <th align="left">
    <B><%=objRSPhase(intCol).Name%></B>
    </th>
    <% Next %>
    </tr>
    <%While Not objRSPhase.EOF %>
    <tr class="grdRow">
    <%For each item in objRSPhase.Fields %>
    <td><%=cstr(objRSPhase(item.Name)) %></td>
    <% Next %>
    </tr>
    <%objRSPhase.MoveNext %>
    <%Wend%>
    </table>
    <%else %>
    <div class="errormsg">No records could be found.</div>
    <%End If%>

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    675
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried modifying the script timeout ?

    http://www.w3schools.com/asp/prop_scripttimeout.asp

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    first at all modify your stored procedure
    alter Proc [dbo].[GetLatestPhasesEditHistory]
    @ProjectID int
    -- [GetLatestPhasesEditHistory] 216
    As Begin

    Declare @new varchar(1000),
    @old varchar(1000),
    @return varchar(1000),
    @i int,
    @tblCnt int,
    @UpdatedDate datetime,
    @UpdatedBy varchar(10),
    @Phase varchar(1000)


    If object_id('dbo.#t') is not null
    Begin
    drop table dbo.#t
    End
    CREATE TABLE dbo.#t
    (
    Phase varchar(1000),
    UpdatedDate datetime,
    UpdatedBy varchar(10)
    )
    --CREATE INDEX IDX_phase ON #t(Id)

    if (select COUNT(*) from phase WHERE PROJECT_ID=@ProjectID ) > 0
    BEGIN
    Set @tblCnt = (select COUNT(*) from phase WHERE PROJECT_ID=@ProjectID )
    Set @i=1
    While @i != (@tblCnt) Begin
    Select top 1 @new = a.phase, @UpdatedDate = a.UpdatedDate, @UpdatedBy = a.UpdatedBy from
    (select Top (@i) phase, ID, UpdatedDate, UpdatedBy
    from Phase
    where project_id = @ProjectID
    order by ID desc)
    a order by a.ID asc

    set @old = (Select top 1 phase
    from
    (select Top (@i + 1) phase,ID
    from Phase
    where project_id=@ProjectID
    order by ID desc) a order by ID asc)
    INSERT INTO #t (Phase)
    exec [dbo].[PMT_GetPhasesEditHistory] @strN = @new,@strO = @old
    --Update #t set UpdatedBy = @UpdatedBy, UpdatedDate=@UpdatedDate where Id = (select max(ID) from #t)
    Set @i = @i +1
    End
    select top 1 @old = a.phase, @UpdatedDate = a.UpdatedDate, @UpdatedBy = a.UpdatedBy
    from (select Top (@tblCnt) phase, ID, UpdatedDate, UpdatedBy
    from Phase
    where project_id=@ProjectID
    order by ID desc)
    a order by a.ID asc

    INSERT INTO #t (Phase,UpdatedDate,UpdatedBy) Values (@old,@UpdatedDate,@UpdatedBy)
    END
    select Phase,UpdatedDate,UpdatedBy from #t
    drop table #t;
    End
    it will run faster
    in your asp code
    change objConnPhase.CommandTimeout = 120 to objConnPhase.CommandTimeout = 0
    0 is no timeout
    add very first line on yoour asp page
    Server.ScriptTimeout=600
    this will set page timeout to 10 minutes

  4. #4
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The fact that it runs fine from the database but not from ASP leads me to think that its more of a connectivity issue between the web server and database server than a programming issue. Are you hosting both the site and the SQL Server on the same physical machine?


Tags for this Thread

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
  •