SQL Timeout and locked entry
Posted on 2008-10-02
My vb.net app has been working for many months and all of a sudden i am getting a timeout message when trying to do an SQL update from my code. If i run from the MSSQL window i can add manually fine.
I notice that when i am adding it freezes and then if i do a sp_who2 it shows a number of SUSPENDED status for my database communications.
im doing a dbase loop and inside that loop in the following code:
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dr As SqlDataReader
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=mypassword;database=databasename")
'you need to provide password for sql server
myCommand = New SqlCommand(sql, myConnection)
dr = myCommand.ExecuteReader
I have tried several sql update methods, and have set both command and connection timeouts to 0 and 1000 of which does nothing (sits there waiting for infinity or times out after 1000 seconds)
Its really bugging me and have no idea what to do. I have rebooted server and restarted services tp np avail