Why does SQLCMD mode seem to execute xp_cmdshell out of sequence when connecting to other servers?

Question

If I connect to server "ServerA.domain.com" and execute the following in SQLCMD mode of SQL Management Studio:

EXEC xp_cmdshell 'hostname'
:CONNECT ServerB.domain.com
EXEC xp_cmdshell 'hostname'

Then the output is:

Connecting to ServerB.domain.com...
output
---------------------------------------
ServerB
NULL

(2 rows affected)

output
------------------------------------------
ServerB
NULL

(2 rows affected)

Disconnecting connection from ServerB.domain.com...

It appears to execute the :CONNECT command prior to the first xp_cmdshell command, despite the sequence of the script. What is the reason for this, and can it be prevented?

Answer 1

It's not obvious from the sqlcmd Commands documentation, but it does provide the hint:

Commands are executed immediately. They are not put in the execution buffer as Transact-SQL statements are.

The implication here is that with the following .sql file:

EXEC xp_cmdshell 'hostname'
:CONNECT ServerB.domain.com
EXEC xp_cmdshell 'hostname'

The :CONNECT ServerB.domain.com gets executed first followed by the two buffered statements EXEC xp_cmdshell 'hostname', which correlates with the behavior you are seeing.

To get the expected behavior you need to add a GO batch separator:

EXEC xp_cmdshell 'hostname'
GO
:CONNECT ServerB.domain.com
EXEC xp_cmdshell 'hostname'