A day before I wrote article SQL SERVER – sqlcmd vs osql – Basic Comparison. Today while I was displaying how sqlcmd can be used instead of osql to one of my companies team leader, I found another neat feature of SSMS Query Editor. sqlcmd can be used from Query Editor but it has to be enabled first.
Following image display how sqlcmd can be enabled in Query Editor. Go to SSMS Menu >> Query >> (click on ) SQLCMD Mode.
Now on Query Editor will support sqlcmd mode. Let us see following example where script contains operating system commands as well SQL commands.
SELECT @@Version ServerVersion !! Dir "C:\Documents and Settings" !! Date /T SELECT GETDATE() CurrentTime
Once above command has ran following output is displayed in Result Window.
Interesting Observation:
What really is interesting is that out of complete batch all the operating system commands runs first and those are followed by SQL commands. The order of SQL and operating system does not matter in resultset.
I want to throw my above observation to all of you and want to get your feedback about that. Have you faced this situation or scenario? What is the solution some we want to execute SQL and OS commands in order?
Watch the video on the same subject:
Reference : Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Here’s a couple for starters – perhaps you might want to rename an old DB backup before running a new one?
Or you might want to control Windows services, stopping one before running some maintenance and then restarting.
Bizarre. I have confirmed this. I wrote a command line program that allows me to use the c# DateTime.ToString and get the full time, I then called the following script:
SET nocount on
SELECT GETDATE() CurrentTime
!! c:toolsdatetime HH:mm:ss.ffffzzz
And it not only puts the results in the opposite order, but it runs them in the opposite order as shown in the results:
10:37:47.8240-05:00
CurrentTime
———————–
2009-01-06 10:37:47.880
Weird.
One can set variable in SQLCMD mode too, if running T-SQL script in SQLCMD mode
:SETVAR ServerName “Server”
:SETVAR ManagementDatabase “MDW”
GO
:CONNECT $(ServerName)
GO
………….usual T-SQL code………….
This situation can be described in few ways. The basic is sqlcmd does not process sql command unless it find a sql terminator (like ‘go’).
Take the following example. You will get the results in desired sequence.
SELECT @@Version ServerVersion
go
!! Dir “C:\Documents and Settings”
!! Date /T
SELECT GETDATE() CurrentTime
go
Resend. Sorry about it.
This is what I got using SB’s snytax, it seems that the OS commands not working properly:
ServerVersion
—————————————————————————————————————————————————————————————————————————————————————-
Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(1 row(s) affected)
The filename, directory name, or volume label syntax is incorrect.
Thu 01/22/2009
CurrentTime
———————–
2009-01-22 14:50:59.353
(1 row(s) affected)
Please advise.
Thank you very much!
Hi all,
i am facing one problem with SQL CMD.
problem is:
I want to automate some SQL CMD script.
For successfull execution of this job i need enable the SQL CMD MODE before executing the SQLCMD script
(like, :connect, :r ….etc.).
I dont know how to enable SQL CMD MODE using “T-SQL” commands.
Please help me as this very urgent/imp to me.
Thanks,
Mohan
I would like to know the answer to Mohan’s question of programmatic access to SqlCMD MODE also…and I would very much like to know how to set a variable dynamically for the :CONNECT command. I already know about :SETVAR but I need to know how to pump a sql variable into :SETVAR:
declare @serv sysname
select @serv=servname from ServerTable
:SETVAR serv @serv –THIS WILL NOT WORK!
:CONNECT $(serv)…
thanks MUCH
-JS
Dear All,
I observed a very interesting thing can anyone please explain this..
After running the query in “Results to text” mode I got the answer in Results tab no matter whether OS command or SQL command
But when i run the query in “Results to Grid” mode I get a new tab as “Messages” for OS commands output
Please explain
Thanx n Regards
Atin
@Atin,
When you see results in Results to Grid : You will see only result set returned by the query in result, but any message attached to it, like command completed successfully or any error that occurred while executing that sql statements, will be displayed in message tab.
When you see results in Results to Text: You will result set returned by query and any message about that query in the same tab.
That is why you will see one more tab called messages when you set your results window to be Results to Grid.
~ IM.
A little late response, but to help any subsequent readers I believe you need to put a GO after each set of SQL commands if you want the output from them to appear in the expected order, otherwise the SQL Batch isn’t completed until after the OS commands have been run.