Will a SQL Server transaction commit if an error happens during the run?

Question

I have a transaction that does many OPENQUERY to many different server. one of those server is MySQL.

If I start transaction, and then insert/update data into multiple linked servers. If an error happened during this transaction (for example linked server is not available) will SQL Server automatically rollback the entire transaction and tells the linked servers to rollback? Or will it commit the transaction anyway, and there is a chance that a data in committed to one server but not others?

BEGIN TRAN T1;
 INSERT INTO OPENQUERY...
 INSERT INTO OPENQUERY....
 INSERT INTO table1.....
 UPDATE table 2 .....
 COMMIT TRAN T1;

Answer 1

Since you don't havea try catch block, yes there is a strong possiblity that the data won't rollback. YOu need to specify what to do in teh case of an error. You should never wrte a transaction without a try catch block and a rollback strategy.