If I run a stored proc using the SqlCommand and the SqlCommand times out does the StoredProc continue to execute or does it get force to quit when the SqlCommand disconnects?
Add a comment
|
2 Answers
My instinct said that the procedure would still execute so I threw together a simple test.
SQL:
Create Procedure TestDelay
AS
waitfor delay '00:00:40'
update table_1
set dt = getdate()
And in VB.Net (same as C# for this purpose):
Dim con As New SqlConnection(myconnectionstring)
Dim com As New SqlCommand("TestDelay", con)
com.CommandType = CommandType.StoredProcedure
con.Open()
Try
com.ExecuteNonQuery()
Catch ex As Exception
con.Close()
Response.Write(ex.Message)
End Try
The result? The procedure did not complete after the timeout. I checked what was happening during a trace in SQL profiler and sure enough SQL appears to wrap the call in a transaction and must roll that transaction back on the timeout.
Note: This test was run against SQL 2005 but I'd suspect the results to be similar in other versions.
1 Comment
gbn
Correct, at least back to SQL 7
As brendan stated, the client sends an "abort" and processing stops dead. As simple as that.
However, it's more complex than that...
Any transactions are not rolled back by default and locks are just left there until the connection is closed. If returned to the connection pool and reused, then this does not count as closing.
This is why SET XACT_ABORT ON (other questions SO1, SO2) is recommended
