Suppose that I use C# to run a long running SQL Server stored procedure (lets say 30 minutes). Further suppose that I put a 1 hour timeout period on the query in C# such that if for whatever reason this SP takes longer than expected, I don't end up monopolizing the DB. Lastly, suppose that this stored procedure has a try/catch block in it to catch errors and do some clean-up should any steps inside it fail.
Some code (C#):
using (SqlCommand comm = new SqlCommand("longrunningstoredproc"))
{
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandTimeout = 3600;
comm.ExecuteNonQuery();
}
/* Note: no transaction is used here, the transactions are inside the stored proc itself. */
T-SQL (basically amounts to the following):
BEGIN TRY
-- initiailize by inserting some rows into a working table somewhere
BEGIN TRANS
-- do long running work
COMMIT TRANS
BEGIN TRANS
-- do long running work
COMMIT TRANS
BEGIN TRANS
-- do long running work
COMMIT TRANS
BEGIN TRANS
-- do long running work
COMMIT TRANS
BEGIN TRANS
-- do long running work
COMMIT TRANS
-- etc.
-- remove the rows from the working table (and set another data point to success)
END TRY
BEGIN CATCH
-- remove the rows from the working table (but don't set the other data point to success)
END CATCH
My question is, what will SQL Server do with the query when the command times out from the C# side? Will it invoke the catch block of the SP, or will it just cut it off altogether such that I would need to perform the clean-up in C# code?
