I have one task that executes Stored Procedure A which inserts and deletes data based on reading another table on another server.
I have another task that executes Stored Procedure B that updates a table on a different server using a different connection manager.
I would like to have that under one commit but everything I read says that it has to be under one connection manager. If I put everything in one Stored Procedure and use the Linked Server to do the update then it is way too slow! So having two separate tasks that are performed under two different connection managers is working very quickly but I would like to make sure that Stored Procedure B gets committed only if Stored Procedure A is successful. And ideally, to rollback if there is a problem with Stored Procedure B.