CODEWITHSUNDEEP
sql-servervb.net
Ruedi.Angehrn
Ruedi.Angehrn

Reputation: 102

Does a stored procedure finish on "Execution Timeout Expired" (VB.Net)?

So I have a nomal System.Data.IDbCommand in VB.Net executing a stored procedure. After thirty seconds (System.Data.IDbCommand default timeout) the applications runs into an "Execution Timeout Expired" exception. So far all good and clear to me.

At the start and at the very end of the stored procedure is an insert to a log table. Both entries to the log table are made which makes me believe that the stored procedure, even after running into the timeout still finishes.

I found a related post, yet the answer in there was not very clear to me: Stored Procedure and Timeout

According to this post, sql itself has no timeout: Handling Timeouts inside stored procedures

Does the stored procedure finish on the server even if I receive a timeout in VB .Net? There are no transactions defined in the stored procedure (nor in VB.Net) and the try-catch does not throw any error (likely since the sql-server itself has no timeout - just the VB.Net connection/command).

Here is a sample of the stored procedure:

ALTER PROCEDURE [dbo].[sp_Whatever] AS BEGIN
    --Does get logged
    INSERT INTO [dbo].[T_Log](Message)
    SELECT 'Started'

    BEGIN TRY
        SET NOCOUNT ON;

        --To provoke a Timeout
        WAITFOR DELAY '00:01'
    END TRY
    BEGIN CATCH
        --Does not get logged (=no error here)
        INSERT INTO [dbo].[T_Log](Message)
        SELECT 'Error'
    END CATCH;

    --Does get logged
    INSERT INTO [dbo].[T_Log](Message)
    SELECT 'Finished'
END;

In the end in my log table I have both entries "Started" and "Finished".

Upvotes: 2

Views: 2221

Answers (1)

Terry Carmen
Terry Carmen

Reputation: 3896

Once sent to the server, your stored procedure will execute until it completes, unless cancelled.

If your client app hits a command timeout, it notifies SQL Server to cancel the operation.

This should roll back any changes, but I wouldn't actually want to depend on this behavior without checking, since what actually happens depends on a few other settings like XACT_ABORT

Here's a very detailed explanation.

https://blogs.msdn.microsoft.com/khen1234/2005/10/20/theres-no-such-thing-as-a-query-timeout/

Something to note is that stopping the command when the client times out is actually another command sent by the client. It's strictly a client-side construct and has nothing to do with anything happening on the server. Nothing on the server has actually timed out.

If everything is working normally, a client timeout should stop the command and depending on the configuration and command, changes should be rolled back.

However if Something Bad happens that actually disconnects the client before it sends the cancel command (power failure, impolite reboot, etc.), execution will continue until complete.

Upvotes: 2

Related Questions