We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL transaction timeing out problem

Medium Priority
172 Views
Last Modified: 2012-05-07
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
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
i have taken a backup of hte database of the server and onto my local machine, so i have been restoring the database and trying to fix the problem - so i do not think some one has got exclusive access to the table

thanks
Ok, try the following command on the database

sp_updatestats

Author

Commented:
hi mohan_sekar:
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?

Author

Commented:
no unfortunately not
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
thanks for your help
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.