@@TRANCOUNT goes to 0 after ado rs.open

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

@@TRANCOUNT goes to 0 after ado rs.open

Author

Topic

ccav
Starting Member

3 Posts

Posted - 2009-05-07 : 15:00:29

Strange problem. Have a function that returns @@trancount. The following code results in trancount going to 0.

Using ADODB library in VBA/access app.

ocmd is ADODB.Command
oConn is custom class wrapping ADODB.Connection and exposes the connection at <object>.connection and returns @@TRANCOUNT at <object>.trancount.
oRS is new adodb.recordset.

oConn was previously instantiated and set to .Mode = adModeReadWrite, .CommandTimeOut = 0, and .IsolationLevel = adXactReadCommitted

backend is sql server 2005

Transactions are named. Xact management is in application, but this is a single user app just used to import some data, so not an issue.

tranindex = oconn.trancount <- results in 2 1 outer/1 inner xact
SET ocmd.activeconnection = oconn.connection
ocmd.commandtext = "dbo.blah"
ocmd.commandtype = adCmdTable
oRS.cursorlocation = adUseServer
oRS.cursortype = adOpenDynamic
oRS.LockType = adLockBatchOptimistic
oRS.Open ocmd <- recordset opens and records from previously committed inner xact are exposed.
tranindex = oConn.TranCount <- returns 0

The ADODB.recordset open has somehow set @@TRANCOUNT to zero. Looking at oRS.commandtext it's select * from dbo.blah...

Will a SELECT statement set @@TRANCOUNT to zero while in a transaction? Or is there a bug in ADO recordset open? This doesn't happen on adodb.recordset.execute, @@TRANCOUNT is maintained normally.

Any help?

ccav
Starting Member

3 Posts

Posted - 2009-05-07 : 15:10:24

Correction in last statement. Doesn't happen on oconn.connection.execute

ccav
Starting Member

3 Posts

Posted - 2009-05-07 : 15:36:17

Solved... found the bug.

Changing ado connection from adUseClient to adUseServer was causing it. Must reset the ado connection when u do that. I guess comingling recordsets client/server will need two different connection objects.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources