SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

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 (http://blog.SQLAuthority.com)

About these ads

12 thoughts on “SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

  1. 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.

    Like

  2. 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:\tools\datetime 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.

    Like

  3. 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………….

    Like

  4. Pingback: SQL SERVER - sqlcmd - Using a Dedicated Administrator Connection to Kill Currently Running Query Journey to SQL Authority with Pinal Dave

  5. 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

    Like

  6. 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!

    Like

  7. 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

    Like

  8. 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

    Like

  9. 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

    Like

  10. @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.

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  12. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s