Switching Database context in transaction

  • Thomas Franz

    Hall of Fame

    Points: 3715

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

    God is real, unless declared integer.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Nice question, thanks Thomas

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

  • Shayn Thomas

    SSCertifiable

    Points: 5637

    Nice and easy one, Cheers Thomas

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

  • MilesC

    Ten Centuries

    Points: 1078

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

  • rchantler

    SSCrazy

    Points: 2125

    Why is switching the db context within a transaction allowed?

  • Thomas Franz

    Hall of Fame

    Points: 3715

    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.

  • rchantler

    SSCrazy

    Points: 2125

    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 7 (of 7 total)

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