I understand that an explicit declaration of a distributed transaction (BEGIN DISTRIBUTED TRANSACTION) does not change the @@TRANCOUNT return value at all (and correct me if I'm wrong, because I was unable to test it by myself due to the lack of MS DTC in my testing platform). But I'm concerned about what could possibly happen with this value when a normal, local transaction, automatically escalates to a distributed one because of a command referring to an external database.
As far as I know, Distributed transactions will not affect @@TRANCOUNT values. MSDN document states:
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
For more detail information, you can refer to the following link:
TechNet Community Support
Anyway, my question was a bit different. Imagine this scenario. I begin a local transaction:
At this point, @@TRANCOUNT value equals 1. That I know.
But now, imagine I do something like this:
INSERT INTO RemoteServer.RemoteDB.RemoteSchema.RemoteDB (RemoteColumnName) VALUES (@RemoteValue)
The transaction becomes a distributed one.
How much is the @@TRANCOUNT RETURN VALUE at this point?
That is the question. Sorry if I wasn't explicit enough.
- Edited by John Michaelson Friday, July 26, 2013 2:46 AM Missed a word