Friday, June 05, 2009

SQLCMD Execution Order

by Andrew Kelly via Andrew Kelly on 6/5/2009 11:53:15 PM

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