Transactions commit during command timeout

From: Mike Vedder (mikevedder_at_online.nospam)
Date: 12/22/04 

Date: Wed, 22 Dec 2004 09:03:05 -0800

I have a VB.NET application that calls VB6 DLL. The DLL calls a SQL stored
procedure. The stored procedure executes a few INSERT statements, which are
surrounded by a 'Begin tran' and 'Commit'.

Here is the relevant VB6 DLL code:
Public Function RunSPReturnInteger(ByVal StoredProcName As String, _
                ByVal ReturnParamName As String, _
                ParamArray params() As Variant) As Variant

        Dim DataConn As ADODB.Connection
        Dim Cmd As ADODB.Command
        Dim AttemptCount As Integer
        Dim Error As ADODB.Error
        Dim ErrorString As String

        On Error GoTo ErrorHandler

10 Set Cmd = New ADODB.Command
20 Set DataConn = OpenDataConnection()

30 With Cmd

40 Set .ActiveConnection = DataConn
50 .CommandText = StoredProcName
60 .CommandType = adCmdStoredProc
70 .CommandTimeout = CommandTimeout

        End With
         
         'the following routine adds the params to the cmd object
80 collectParams Cmd, params

        ' Execute without a resulting recordset and
        'pull out the "return value" parameter
90 Cmd.Execute , , ADODB.adExecuteNoRecords

100 RunSPReturnInteger = Cmd.Parameters(ReturnParamName).Value

        ' Cleanup and return
110 DataConn.Close
120 Set DataConn = Nothing
130 Set Cmd = Nothing

        Exit Function

ErrorHandler: 'removed for this post
End Function

The following error occasionally gets raised on line 90:
Number(-2147217871)
Source(Microsoft OLE DB Provider for SQL Server)
SQLState(HYT00)
Description(Timeout expired)

However, about half the time that the error is raised, I find that the
transaction in the stored procedure is committed. My program has no way of
knowing the records were inserted.

My main question is: Why does the transaction get committed on a timeout,
and how can I prevent this?

(I would post the stored procedure code, but it is rather lengthy. I should
note that the stored procedure does not have a 'rollback tran' command. Is
this necessary? Does a timeout need to be explicitly checked for in a stored
procedure and then the rollback command called?)

We are using SQL2000, MDAC 2.8, and .NET 1.1 SP1

The stored procedure gets called about 10,000 times a day. The timeouts
have averaged about 70 per month with the transaction being committed about a
third of that. The timeouts usually occur in bursts (i.e. we might have 20
at once, when there is a problem on the network).

Thanks,
Any help is appreciated.

Relevant Pages

  • Re: SQL/ADO (2.8) Timeout Error - Cant resolve
    ... > Server 2000 back end. ... > timeout limit. ... And I’ve used SQL Query Analyzer to run the ... I am running a stored procedure that will backup a SQL Server ...
    (microsoft.public.sqlserver.clients)
  • Re: ADO.NET Timout
    ... So the 30 sec Connection Timeout will have no effect when a SP called ... Do I still have to give it a higher time value? ... How can I call a stored procedure to do its thing asynchronously. ... But what about SQL Server Does SQL Server keep on Executing the Stored ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Capturing SQL Stored Procedure Return Codes
    ... Set cmd = New ADODB.Command ... intReturnValue = cmd.Execute ... When calling a stored procedure from Access, how do you capture this return ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO.NET Timout
    ... Sure it will (timeout). ... What I am trying to do is call a SP and let it execute no matter how long ... How can I call a stored procedure to do its thing asynchronously. ... But what about SQL Server Does SQL Server keep on Executing the Stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: how to determine the best timeout value
    ... Are you sure you are not being blocked when you timeout? ... > an xml document that is passed to the stored procedure in a string. ... > If I run the code that executes the stored procedure it executes fine ...
    (microsoft.public.sqlserver.connect)