SQL Server Rollback nested Stored procedures and linked servers

Question

I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to do something very similar to the question in that article (Rollback everything from the parent stored proc, as well as what occurred in the sub-proc, if the sub-proc fails), except that my sub-proc does simple inserts into another SQL Server via a linked server.

Do any of the nuances of transactions and rollbacks change in this instance?

As a side note, we are using AWS EC2 with SQL Server 2008 R2, and we had problems configuring MSDTC a couple months ago. We tried using BEGIN DISTRIBUTED TRANSACTION and such, and could not get MSDTC to play nice despite being able to do simple inserts into the linked server easily without the distributed transaction. Must I use Distributed Transactions, or is the another way around it?

Answer 1

Yes you must use a distributed transaction for this since what you described is a distributed query (transaction across servers). You may want to post a new question regarding your MSDTC issue in EC2.

• Jeff.Clark: Bummer. Way to ruin my day :) What would happen if I used BEGIN TRANSACTION in the main stored procedure, and an error happened in the sub-procedure linked query? Would it bypass rolling back the sub-procedure, and continue on to rollback the parent stored procedure?
• Tara Kizer: I haven't tested it, but I think it will rollback the transaction on just the local server and the transaction on the remote server will be committed.