Solved

SQL Server Timeout Expired When Running 3 Transactions

Posted on 2004-09-02
8
484 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
Comment Utility
you should have an increement before ending the loop ...

like the following

   RS.MoveNext
End With
0
 
LVL 3

Expert Comment

by:Ayesha_K
Comment Utility
oh sorry ... i see it at the end now ...

0
 
LVL 2

Accepted Solution

by:
cheyennejk earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

10 Experts available now in Live!

Get 1:1 Help Now