Solved

SQL Server Timeout Expired When Running 3 Transactions

Posted on 2004-09-02
8
485 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now