SQL Server Timeout Expired When Running 3 Transactions
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
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
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
like the following
RS.MoveNext
End With