THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

SQLCMD Execution Order

I find that many people don’t realize since SQL Server 2005 that you have the ability to execute SQLCMD commands or scripts directly in the Query Editor but it can come in handy at times. Even I don’t use it that as much as I should and I am aware of it:).  One of the things I find it most useful for is during demos at presentations when I want to invoke an OS level command without having to use xp_cmdshell or opening a DOS window.  For instance once you have enabled SQLCMD mode in the Query Editor you can issue this command to look at the contents of a folder:

!! DIR C:\*.*

That is all well and good but one thing I didn’t know until recently is that the SQLCMD commands always execute before any TSQL commands in the batch. I usually run the commands one line at a time so this was never an issue and as such I never paid much attention to the order of execution. If you run this simple example on your machine you can see the results in person.

PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:02' ;

PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

!! systeminfo

PRINT '3: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:02' ;

PRINT '4: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

The results will look similar to this:

 

Host Name:                 LAPTOP_IBM
OS Name:                   Microsoftr Windows VistaT Ultimate
OS Version:                6.0.6002 Service Pack 2 Build 6002
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Standalone Workstation

Network Card(s):           3 NIC(s) Installed.
                           [01]: Intel(R) 82566MM Gigabit Network Connection
                                 Connection Name: Local Area Connection
                                 DHCP Enabled:    Yes
                                 DHCP Server:     192.168.1.1
                                 IP address(es)
                                 [01]: 192.168.1.100
                                 [02]: fe80::3ca9:45fd:f7e4:c5e2

1: 2009-06-05 19:42:37.403
2: 2009-06-05 19:42:39.400
3: 2009-06-05 19:42:39.400
4: 2009-06-05 19:42:41.400

 

Note that the PRINT statements are all after the output of the systeminfo command even though it fell in between the PRINTS and the WAITFOR’s. I added the WIATFOR’s just so you can see there was a gap in between the first and last two PRINT’s yet no gap where the SQLCMD should have run.  If you are still not convinced you can also rn this demo as well:

PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:10' ;
!! time

PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

You will have to hit the stop button to kill the batch since the time command is waiting for input. But it does show that the 1st PRINT statement or the WAITFOR never ran as the only output when you cancel the batch is the actual time output from the time command.  While this may not be news for some of you I bet it is to most and I figured it was a nice little tidbit to share.

Have fun,

Andy

Published Friday, June 05, 2009 8:53 PM by Andrew Kelly
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Greg Low said:

Hi Andy,

A bunch of us were discussing this weird behaviour at TechEd. The one that has caught me is that if you use the SQLCMD command to change servers, it's not treated as an implicit batch separator! So if you select a server, execute a command, select another server, execute a command, etc. and forget to put in GO all over the place, *all* the commands are executed against the last server.

In some bizarre world, this might be *correct* behaviour, but it's sure not expected behaviour.

Regards,

Greg

June 6, 2009 1:51 AM
 

Saggi Neumann said:

As Greg mentioned, I too noticed this in a SQLCMD script that changed the connection somewhere in the middle (setting up remote distributor, transactional replication publication and a pull subscription or something like that).

The solution is of course simple - for safety I just wrap any SQLCMD command with GO before and after it (only one really is required but I never remember which).

Again, I support Greg's opinion - the default behavior with SQLCMD really isn't the expected behavior.

June 6, 2009 3:46 PM
 

Nick Beagley said:

It's important to remember that !! commands are executed on the computer on which sqlcmd/ssms is running, not the sql server to which it is currently connected, so

!! systeminfo

is only the same as

exec xp_cmdshell 'systeminfo'

if you are logged on to the physical host of the sql server instance and running sqlcmd/ssms there. When running sqlcmd/ssms on your workstation any !! commands apply to your workstation whereas xp_cmdshell commands are run on the sql server host.

June 11, 2009 2:10 AM
 

不晓得 said:

你们在说什么?

I can't understand

不会说英语

我的英语老差了

但是我很想学这个SQL,我都一个人学了N久了

但是就是搞不懂好多东西

Who can help me?

June 15, 2009 7:36 AM
 

Adam Machanic said:

你为什么不尝试中的SQL Server博客?

June 16, 2009 9:57 AM
 

Jason said:

我抱歉,但我不可帮助您。  我对SQL讲中文,但I无知者。  

好运

June 16, 2009 10:02 AM
 

JOn said:

These don't work in my SSMS 2008 query window.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '!'.

June 19, 2009 8:35 AM
 

David Lean said:

Jon: If you are within SSMS you need to turn on SQLCMD mode.

This can be done by clicking Menu: Query => SQLCMD Mode

or clicking the SQLCMD button on the toolbar; It looks like a Table with a red '!' character on it.

July 19, 2009 3:59 AM
 

Workstation version 6.0 Service Pack 2 (Build 6002) said:

Workstation  version 6.0 Service Pack 2 (Build 6002)

December 22, 2010 5:18 PM
 

Steph said:

Does not work for me  i mean the prefix !!

i'm running SQL SSMS 2008 and no way

i'm connect to an instance with xp_cmdshell DISABLED

i did try to execute !! systeminfo but the results is simply

returned from ly local machine where i have SSMS opened but not the result when SSMS is connected to..

Am i missing something here ??

Thanks

Steph

September 24, 2012 5:43 AM
 

Andrew Kelly said:

Steph, Please read the comment from Nick in which he reminds us that any SQLCmd commands are always run on your local machine and not the one you may be connected to remotely via SSMS. This is expected behaviour.

September 24, 2012 9:12 AM
 

Michael Russ said:

This is a late post, but hey I wasn't consulted earlier...

Good blog about the execution order. If you add "<nul" to the time command, then it won't wait for input, which makes demoing the execution order easier. Check this out (notice how the "go" impacts the execution):

!! time <nul

PRINT ''

PRINT '1: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:02' ;

PRINT '2: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

!! time <nul

go

!! time <nul

PRINT ''

PRINT '3: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

WAITFOR DELAY '00:00:02' ;

PRINT '4: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

!! time <nul

October 23, 2013 1:50 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement