SQLCMD connection switching

  • balde

    SSCommitted

    Points: 1594

    Comments posted to this topic are about the item SQLCMD connection switching

  • This was removed by the editor as SPAM

  • Hany Helmy

    SSChampion

    Points: 13488

    Good question, I like it 🙂

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    I am completely baffled by being able to get a result for Server2 before it has even been connected to. Can somebody give a better explanation? The one provided is insufficient for me.

  • David McKinney

    SSChampion

    Points: 10449

    tom.w.brannon (10/27/2015)


    I am completely baffled by being able to get a result for Server2 before it has even been connected to. Can somebody give a better explanation? The one provided is insufficient for me.

    I agree with Tom..surely answers 2 and 3 are equally plausible. It just depends on what your original connection was?

  • Neil Burton

    SSC-Insane

    Points: 22229

    David McKinney (10/27/2015)


    tom.w.brannon (10/27/2015)


    I am completely baffled by being able to get a result for Server2 before it has even been connected to. Can somebody give a better explanation? The one provided is insufficient for me.

    I agree with Tom..surely answers 2 and 3 are equally plausible. It just depends on what your original connection was?

    +1


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Ed Wagner

    SSC Guru

    Points: 286982

    I never use it, but perhaps it's time for me to look into it.

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    The Messages pane gives some insight. Without GO statements, this is one SQL batch. The connect commands are processed in order before any of the SQL is executed.

    Connecting to server1\instance1...

    Disconnecting connection from server1\instance1...

    Connecting to server1\instance2...

    Disconnecting connection from server1\instance2...

    Connecting to server2\instance1...

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    Disconnecting connection from server2\instance1...

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    Stephanie Giovannini (10/27/2015)


    The Messages pane gives some insight. Without GO statements, this is one SQL batch. The connect commands are processed in order before any of the SQL is executed.

    Connecting to server1\instance1...

    Disconnecting connection from server1\instance1...

    Connecting to server1\instance2...

    Disconnecting connection from server1\instance2...

    Connecting to server2\instance1...

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    Disconnecting connection from server2\instance1...

    Thank you! Now I understand.

  • David McKinney

    SSChampion

    Points: 10449

    Thanks, Stephanie....makes sense at last!

  • wally 96967

    SSC Enthusiast

    Points: 192

    However nowhere does it say we start in Server2/Instance1

  • Junior Galvão - MVP

    SSCertifiable

    Points: 6527

    Hi, Paul.

    Great question, I did not know the correct altenartive.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good to know, thanks.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    wally 96967 (10/27/2015)


    However nowhere does it say we start in Server2/Instance1

    It doesn't, but that is not really relevant. When parsing the batch, SSMS will execute all the :connect commands in order, so it will end up connected to the last mentioned server regardless of where it was connected first. And then it will send the T-SQL part of the batch to the instance it is connected to.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 17 total)

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