[dba-SQLServer] Table lock with uncommitted BeginTrans

Hi Jim

Thanks, but this is DAO with "normal" select and action queries and some pass-through queries for special tasks, no stored procedures.

/gustav

>>> jlawrenc1 at shaw.ca 29-06-13 17:40 >>>
Hi Gustav:

Question: Why would a BeginTrans be executed without the follow up of a
RollBack/CommitTrans? This probably is what is locking the table.

I am no expert in ODBC, as I only ever use ADO, it is faster and has never
failed in almost 20 years. Example:

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
    
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command
    
objConn.Open gstrConnectionString
objConn.BeginTrans
    
With objCmd
      .ActiveConnection = objConn
      .CommandType = adCmdStoredProc
      .CommandText = "StoredProcedureName1"
      .Parameters.Append .CreateParameter("@intCode1", adInteger, adParamInput, , lngCode2)
End With
        
objCmd.Execute
objConn.CommitTrans
...

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Saturday, June 29, 2013 1:58 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Table lock with uncommitted BeginTrans

Hi all

A table didn't respond at the client's SQL Server.
The dba told that it was locked by a uncomitted transaction, and a restart
of the SQL Server was needed. That happened and the table was accessible
again.

This table, however, is only used by an Access app via ODBC, and nowhere is
BeginTrans used for the table. As to my understanding, SQL Server performs
AutoCommit if not told otherwise.

So, questions:

1. How can a BeginTrans be applied without a RollBack/CommitTrans?
2. Is it really necessary to restart the SQL Server to unlock the table?

Any other suggestions what to look for or how to prevent this situation?

/gustav