Begin Tran open disconnected recordset - reconnect - rollback/

Begin Tran open disconnected recordset - reconnect - rollback/

Post by SmFtaW » Thu, 28 Feb 2008 20:13:01



I thought the key was the connection. Good to see it in print before
upgrading a critical legacy app.

Thanks Stephen.
 
 
 

Begin Tran open disconnected recordset - reconnect - rollback/

Post by SmFtaW » Thu, 28 Feb 2008 21:10:00

ust to continue on this line of thought. There are actually two
co-dependent connections in the legacy application [which used DAO]. It tied
the two together using a Workspaces object that was ODBC bound to an ACCESS
database through table linking with many tables from the SQL Server 7.0
databases. The queries ran through this ODBC connection channel and the
rollback was only through the single ODBC channel.

While I can still follow the rule to open the connection and rollback
depending on the presence of the COM exception - I suspect that for this
case, what must happed is this - Establish a connection for each recordset,
maintain both, begin tran on both, and commit or rollback depending on
whether one or each of them has a COM exception.

Is this correct?

"Jamie" wrote:


 
 
 

Begin Tran open disconnected recordset - reconnect - rollback/

Post by Stephen Ho » Fri, 29 Feb 2008 03:51:14

> Just to continue on this line of thought. There are actually two

But that is flawed.
You will have to commit/rollback one Recordset before the other.

What happens if you commit RecordSet 1 and it succeeds and you commit
RecordSet 2 and it fails?
You then want to rollback Recordset 1 and it is too late.

I also dont know what happens with registered linked servers and
transactions - more research would be needed (but I would suspect they are
not covered).

I dont know what the best solution is for this.
Research on registered linked servers and transactions would be my first
line of attack.

Cheers

Stephen Howe
 
 
 

Begin Tran open disconnected recordset - reconnect - rollback/

Post by SmFtaW » Fri, 29 Feb 2008 04:09:04


Interesting... As the records are in the disconnected recordset, I suspect I
can force it back to its state when it was disconnected. I was hoping I
wouldn't need to go into that much detail but as I have the set disconnected,
the orginalvalue will show along with the value... I can update with the
originalvalue to set it back to pre-commit. Ughh... hope I don't need to go
that far.
Linked tables was the problem actually - the Access linked the tables (not
servers) with Access acting as a surrogate "server" for those tables with the
connection being to the Access database. If we were in SQL 2005, we could
almost call them "Aliases".

My approach is to remove ACCESS from the picture along with the DAO
Workspace (where the commit and rollback are set in the legacy code) and
replace this with simple connections. I'll look at it some more. Generally
legacy applications have logic that is held back for technological reasons
and there is always an easier way now to do what was not possible then.

Thanks for helping.