Switching Database context in transaction

  • Comments posted to this topic are about the item Switching Database context in transaction

    God is real, unless declared integer.

  • Nice question, thanks Thomas

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Nice and easy one, Cheers Thomas

    The more you know, the more you know that you dont know

  • I agree, nice and easy one, thx Thomas!!

  • Why is switching the db context within a transaction allowed?

  • Counterquestion: why should it be forbitten? It does not write anything to the log, changes no data ...

    I don't know how it is realized internal, but theoretical an USE [db] is only a compiler hint, that adds the current used database before any object declared only with schema + object name when creating the execution plan. And since it is fully okay to mix different databases inside an transaction by using the [db].[schema].

    -syntax, the same should be true for the "hint" created by the USE statement.

    God is real, unless declared integer.

  • Thank you, I like your explanation of the USE statement being a hint.  But wouldn't that also imply you should be able to change the database context within a stored procedure without resorting to dynamic SQL?

    It seems like a logical inconsistency between how scripts execute in SSMS vs running a stored procedure. In the past we observed the similar errors in error handling, i.e. different behaviour in SMSS vs running a stored procedure.

    I know that a foolish consistency is the hobgoblin of little minds, but ...



Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply