0

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?

ir8008
  • 3
  • 2
  • The two `EXEC xp_cmdshell 'hostname'` are in the same batch. Do you get expected behavior if you insert a `GO` batch separator line before your `:CONNECT` line? – AlwaysLearning Aug 10 '21 at 05:11
  • Adding a batch separator does give the result I am looking for. Even though it does not (in my opinion) answer the question of why SQL seems to execute the statements out of order, it does solve the problem I was having. So, if you would like to post that as an answer I will mark it accepted. – ir8008 Aug 11 '21 at 15:55

1 Answers1

2

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'
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35