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

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

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?

Solution

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'