SQLCMD connection switching

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

  • This was removed by the editor as SPAM

  • Good question, I like it 🙂

  • 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.

  • 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?

  • 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

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

  • 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...

  • 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.

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

  • However nowhere does it say we start in Server2/Instance1

  • Hi, Paul.

    Great question, I did not know the correct altenartive.

  • Good to know, thanks.

  • 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/

  • 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