If you establish one or more remote (proxy) database connections from a "master" connection on the local database, and then issue a COMMIT on that master connection, COMMIT operations are sent to ALL the remote database connections.
...still, after all these years using proxy tables, this behavior can reach out and Whack! you :)
It would be valuable and useful to be able to decouple remote database transaction design from that of the local database... perhaps as an additional non-default keyword such as COMMIT LOCAL ONLY.
Perhaps documenting this COMMIT side-effect would be sufficient, in two places: in the COMMIT syntax Help topic, and in the general discussion of "remote data access".
If the remote servers are used for read access only, then you would be better off issuing create server statements that contained the read only clause. SA will not propagate any commit or rollback to remote servers that are explicitly marked as read only.
answered 09 Aug '10, 18:10