Solved

SQL Server Timeout Expired When Running 3 Transactions

Posted on 2004-09-02
8
487 Views
Last Modified: 2012-06-21
Can anyone help me to point the error in the following code? I have 3 transactions to 2 different database server. All transactions have to be TRUE before their committed, if no all the previous modifications will be roll back. However, when i try to run the code, I'm getting the following error even though I'm just testing it on 2 rows of sample data. This process is supposed to handle hundreds of records, please help.

Microsoft OLE DB Provider for SQL Server error '80040e31'

Timeout expired




<%
Dim CharCon

Set CharCon=Server.CreateObject("Adodb.Connection")

CharCon.open CharConString

strSql="Select * from Character_Info where Trans_Status='1'"

Set Rs=CharCon.Execute(strSql)


If Rs.Eof<> True Then
 
  While Rs.Eof<> True
     
         Receiver=Trim(Rs("Receiver"))
         
         Sender       =Trim(Rs("Sender"))
       
         Server_Name  =Trim(Rs("Server"))
       
         Nation_Name  =Trim(Rs("Nation"))
       
         Character_Name=Trim(Rs("Character"))
             
     
     

     
            Set myCon=Server.CreateObject("Adodb.Connection")
         
            myCon.Open CharConString
           
            myCon.BeginTrans
           
            myCon.Execute "Update Character_Info set Trans_Status='9' where Trans_Status='1' And Sender='" & Sender & "' And Receiver='" & Receiver &"' And Character='" & Character_Name &"'And Nation='" & Nation_Name &"' And Server='" & Server_Name & "'",ingrecs
           
            If myCon.Errors.count>0 or IngRecs=0  Then
             
              myconres=false
             
              myCon.RollbackTrans
              set mycon=nothing
 
            Else
 
             myConres=true
 
            End If
           
                  If  myconres=true then
               
                      Set myCon1=createobject("adodb.connection")
                 
                      myCon1.ConnectionString= Connection_String
                 
                      myCon1.Open
                 
                      myCon1.BeginTrans
                   
                                Set cmdDB = Server.CreateObject("ADODB.Command")
                         With cmdDB
                        .ActiveConnection = myCon1
                        .CommandText = "ACCOUNT_CHAR_INSERT_CHAR"
                        .CommandType = adCmdStoredProc

                        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
                        .Parameters.Append .CreateParameter("@Receiver", adVarChar, adParamInput, 50,Receiver)
                        .Parameters.Append .CreateParameter("@Character_Name ", adVarChar, adParamInput, 50,Character_Name)
                        .Execute,,adExecuteNoRecords
                  
      
                        RetVal = Trim(.Parameters("RETURN_VALUE"))
      
                  
                        Set .ActiveConnection = Nothing
                        End With
                       
             
                    Else
             
                      myCon1res=False
                  End If  
           
                                   
              if myCon1.Errors.count>0 Then
                myCon1res=False
             
              Else
               
                 If myCon1res<>False or myCon1res ="" Then
                     
                   myCon1res=True
                 
                                                               
                                   
                Else
                       
                   myCon1res=False
                   mycon1.RollbackTrans
                   set mycon1=nothing
                                   
                       
                End If                
                     
                     
             End If
           
     
           
            If mycon1res=True then
           Response.Write mycon1res
                           
         Set myCon2=createobject("adodb.connection")
                 
                 myCon2.ConnectionString= Connection_String
                 
                 myCon2.Open
                 
                 myCon2.BeginTrans
                   
                Set cmdDB = Server.CreateObject("ADODB.Command")
     With cmdDB
      .ActiveConnection = myCon2
      .CommandText = "ACCOUNT_CHAR_REMOVE_SENT_CHAR"
      .CommandType = adCmdStoredProc

      .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
      .Parameters.Append .CreateParameter("@Sender", adVarChar, adParamInput, 50,Sender)
      .Parameters.Append .CreateParameter("@Character_Name ", adVarChar, adParamInput, 50,Character_Name)

      

      .Execute,,adExecuteNoRecords <---- timeout expired at this line ------ >
                  
      
      RetVal = Trim(.Parameters("RETURN_VALUE"))
      
                  
      Set .ActiveConnection = Nothing
End With
                           
                 
                 
              Else
                    Response.Write "<br>Not Entered<br>"
                   
                    myconres2=False
             
             End If
                 
             If myCon2.Errors.count>0   Then
             
                    myconres2=false
                    mycon2.RollbackTrans
                    set mycon2=nothing
                 
                                       
   
             Else
               
                 If myConres2<>False or myConres2 ="" Then
                     
                   myConres2=True
                   
                                                               
                                   
                Else
                       
                   myConres2=False
                   mycon2.RollbackTrans
                   set mycon2=nothing
                                   
                       
                End If                
                       
            if myconres=true and mycon1res=true and myconres2=true then
           
            mycon.committrans
            set mycon=nothing
            mycon1.CommitTrans
            set mycon1=nothing
            mycon2.CommitTrans
            set mycon2=nothing

           else

           mycon.rollbacktrans
           set mycon=nothing
           mycon1.rollbacktrans
           mycon1.rollbacktrans
           set mycon1=nothing
           mycon2.rollbacktrans
           set mycon2=nothing
           
           end if
       
       End If      
             
     
         
         
               

RS.MoveNext  
WEND

END If

%>
0
Comment
Question by:dpurple
  • 2
  • 2
8 Comments
 
LVL 3

Expert Comment

by:Ayesha_K
ID: 11970901
you should have an increement before ending the loop ...

like the following

   RS.MoveNext
End With
0
 
LVL 3

Expert Comment

by:Ayesha_K
ID: 11970904
oh sorry ... i see it at the end now ...

0
 
LVL 2

Accepted Solution

by:
cheyennejk earned 250 total points
ID: 11973082
Two things

#1: Its good practice to check if a session is still active at the beggining of the code before executing a set of transactions


<% If Session("active") <> "true" Then
Response.Redirect "../../log_off/Timeout.asp"
End If %>



#2: For critical processes , change the server time out setting for that page to aviod the error

Server.ScriptTimeOut = 100000
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 11981099
It really depends how you want to go about this:
A. Mask the problem by setting the CommandTimeout to a very large value or 0 (infinite timeout)
B. Fix the problem, by addressing the many issues in your code, such as:
1. Opening a Connection in a loop
2. Never explicitly closing the Connection
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12749283
The split is fine with me, but it should be noted for all future readers that the error:
Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired

Is a database connection timeout, not an ASP script timeout (no amount of time set with the Server.ScriptTimeOut will help that)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question