Changing current database during local tx or xa tx

What are the consequences of changing the current database (using "use dbnam
e")
during a transaction?
If I modify multiple databases (on the same Sql Server instance) during a
transaction, will all changes be done in the same transaction (and commited/
rollbacked
atomically)?
Does using XA tx change the answer to the question?Taras Tielkes (taras.tielkes@.gmail.com) writes:
> What are the consequences of changing the current database (using "use
> dbname") during a transaction?
> If I modify multiple databases (on the same Sql Server instance) during
> a transaction, will all changes be done in the same transaction (and
> commited/rollbacked atomically)?
Yes.

> Does using XA tx change the answer to the question?
XA?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Taras Tielkes wrote:
> What are the consequences of changing the current database (using
> "use dbname") during a transaction?
> If I modify multiple databases (on the same Sql Server instance)
> during a transaction, will all changes be done in the same
> transaction (and commited/rollbacked atomically)?
> Does using XA tx change the answer to the question?
Yes. Same transaction. But you cannot use USE statements in stored
procedures or triggers. There's a better way, however. USE, which
physically changes a user's context, is an expensive operation. You're
just better off fully-qualifying the objects you are modifying. Then you
don't have to switch databases:
For example:
Update MyDB.dbo.MyTable SET ...
David Gugick
Quest Software
www.imceda.com
www.quest.com