[dba-SQLServer] Table lock with uncommitted BeginTrans

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



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com