CraigLazar
asked on
SQL transaction timeing out problem
Hi i am using vb.net vs2008. I have a command object which executes a simple update statement on a table that has 100 000 records in it.
For some reason today it has started timeing out which it has not done before
I then took the UPDATE statement and ran it on SQL management studio and it is timing out there now as well.
UPDATE tblACBTrans set Status = 'Rejected' , UnpaidDate = '2009/07/02' where AutoID = 2829442;
I have created a separate Index non-clustered Unique index on my field AutoID which is also my primary key for this table. Still no joy
This is my code in vb to execute the statement, i tried setting the CommandTimeOut property to 60 but did not help
Dim command As New SqlCommand(SQLString, cn)
command.CommandTimeout = 60
Try
If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then cn.Open()
If cn.State = ConnectionState.Open Then
command.ExecuteNonQuery()
ModifyData = "Success"
End If
Catch exSQL As SqlException
msgbox exsql.message.tostring
Finally
command.Dispose()
command = Nothing
End Try
thanks, any help would be great
thanks
For some reason today it has started timeing out which it has not done before
I then took the UPDATE statement and ran it on SQL management studio and it is timing out there now as well.
UPDATE tblACBTrans set Status = 'Rejected' , UnpaidDate = '2009/07/02' where AutoID = 2829442;
I have created a separate Index non-clustered Unique index on my field AutoID which is also my primary key for this table. Still no joy
This is my code in vb to execute the statement, i tried setting the CommandTimeOut property to 60 but did not help
Dim command As New SqlCommand(SQLString, cn)
command.CommandTimeout = 60
Try
If cn.State = ConnectionState.Broken Or cn.State = ConnectionState.Closed Then cn.Open()
If cn.State = ConnectionState.Open Then
command.ExecuteNonQuery()
ModifyData = "Success"
End If
Catch exSQL As SqlException
msgbox exsql.message.tostring
Finally
command.Dispose()
command = Nothing
End Try
thanks, any help would be great
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok, try the following command on the database
sp_updatestats
sp_updatestats
ASKER
hi mohan_sekar:
what does the sp do?
what does the sp do?
It updates the statistics. Since you said the database was restored from a backup, I thought updating stats might help. Did it?
ASKER
no unfortunately not
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Ok if i run my query from management studio it seems to be running fine. However from my vb app it is timing out. I restored yesterdays database and ran the same process using my vb app and it ran with no problems. So i cannot help thinking that there could possibly be some sort of table corruption in todays database compared with yesterdays. I have made now code adjustments to the application, so its very strange.
Are there sp's that i can run to redo/check table indexes, similar to Access's compact and repare database functions?
thanks
Ok if i run my query from management studio it seems to be running fine. However from my vb app it is timing out. I restored yesterdays database and ran the same process using my vb app and it ran with no problems. So i cannot help thinking that there could possibly be some sort of table corruption in todays database compared with yesterdays. I have made now code adjustments to the application, so its very strange.
Are there sp's that i can run to redo/check table indexes, similar to Access's compact and repare database functions?
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Guys,
Ok i eventually found a work around for my problem. I cannot explain why it was doing it but for some reason it was timing out on my SQL reader adapter, so i then switched to using a data table as my container for the records based on my sql statement and it seems to have fixed it. very very odd
thanks anyway for the help
cheers
Ok i eventually found a work around for my problem. I cannot explain why it was doing it but for some reason it was timing out on my SQL reader adapter, so i then switched to using a data table as my container for the records based on my sql statement and it seems to have fixed it. very very odd
thanks anyway for the help
cheers
ASKER
thanks for your help
ASKER
thanks