Stored Proc and SqlCommand Timeout

Question

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?

Answer 1

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.