Post reply

SQL CMD

  • February 8, 2011 at 11:47 am

    #1283931

    Steve Jones - SSC Editor (2/8/2011)

    Cedric-295411 (2/8/2011)

    I am sorry but the question is flawed , the correct command is in fact "!!DIR" with a double exclamation mark and not a single one "!DIR" like the QoD states, as a result I answered NO .Please review questions before posting

    Apologies for the typo, which has been corrected, but it says "things like "!DIR", not that command specifically. It asks about specific SQLCMD functions, and do answer "no" saying that this isn't a valid command appears to be completely misreading the question.

    There seem to be two types of people on here - those who take everything literally and get caught out by typos, and those (like myself) who almost subconsciously "correct" the question and therefore get caught out by questions where the trick is in the syntax. You'll never please everyone!

    Thanks again for the question - it taught me something new and means I don't have to enable xp_cmdshell just to do things like that from SSMS.

  • February 8, 2011 at 12:02 pm

    #1283950

    Great question and I learned something today.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 8, 2011 at 12:25 pm

    #1283980

    Thanks for the question, I haven't used SQLCMD mode before, do many people use it?

  • February 8, 2011 at 1:48 pm

    #1284029

    Great Question.....

    However, I executed the below two queries, but, getting the below error...

    :out C:\testoutput.txt

    SELECT @@VERSION As 'Server Version'

    !!DIR

    !!:GO

    SELECT @@SERVERNAME AS 'Server Name'

    GO

    Error Msg:

    ----------------

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near ':'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '!'.

    Where I'm wrong ? Please suggest...

    Thanks.

  • February 8, 2011 at 1:52 pm

    #1284034

    Did you switch to CMD mode?

    From menu: Query -> SQLCMD Mode

    --Vadim R.

  • February 8, 2011 at 2:01 pm

    #1284042

    rVadim (2/8/2011)

    Did you switch to CMD mode?

    From menu: Query -> SQLCMD Mode

    or you can set it permanently in SSMS by going to

    Tools >> Options >> Query Execution >> SQL Server >> General

    and check "By default, open new queries in SQLSMD mode".

    (The above is for SQL Server 2005, not sure if 2008 is any different)

    --Vadim R.

  • February 8, 2011 at 2:20 pm

    #1284065

    Nice interesting question, thanks.

    Maybe the explanation could have mentioned the limitations: no interactive commands, behaviour of a query in SSMS may be different that for the same query in SQLCMD utility, enabling SQLCMD disables intellisense (that's maybe a reason to enable it :cool:), and enabling SQLCMD disables the T-SQL debugger (that's probably a reason not to).

    Tom

  • February 8, 2011 at 5:07 pm

    #1284133

    UMG Developer (2/8/2011)

    Thanks for the question, I haven't used SQLCMD mode before, do many people use it?

    I develop an application that generates SQL scripts. These scripts should be tested on a dev database and eventually executed unchanged on a production database. I don't want to use dynamic SQL. So I generate these scripts to be executed with sqlcmd.exe - everywhere a database is referenced (mostly in a USE statement at the start of the script), I use USE $(Database);

    On my desktop, there are links to CMD files, that basically take an SQL file and feed it to sqlcmd.exe, with a parameter such as -v Database="DevDB". And another one for -v Database="ProdDB" - you get the picture. So I only need to drag a generated script file and drop it on the appropriate icon to have it executed in the correct server and database context.

    Now, if my generated script contains an error, the output from these files is less than helpful. Sure: error so and so on line 71. Great - in a generated script that contains hundreds of batches. So in those cases, I load the script in SSMS, set it in SQLCMD mode, add a line ":SETVAR Database DevDB" at the start, hit execute, and double-click the error to be immediately taken to the offending line.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • February 8, 2011 at 5:29 pm

    #1284136

    Hugo,

    Thanks for the excellent usage example, that makes a lot of sense.

    We use a separate Dev server with the same DB names, so we just have to execute the scripts, unmodified, while connected to a different server.

  • February 9, 2011 at 4:22 am

    #1284323

    Good question. Tx

    Thanks

  • February 10, 2011 at 12:56 am

    #1284976

    When I read the question ,the fact that the answer to the first part is "Yes" and the second part is "No" ,inherently causes one to question the QoD in this case .However I read the question in it's totality , and a Yes && No in this case led me to deduce a "No" ,I have used the command before in SLCMD mode and therefore knew that the correct command is in fact !!DIR

    Leave that as it be , so no questions in the QoD should not be taken literally ? Or some should an some shouldn't ??? (lesson learned in that case !!)

    Steve --> Was a very insightful question ,so no harm done ,just tried to highlight the part that could cause confusion and did in my case

    Thanks all for contributions ,still learned a lot .

  • February 10, 2011 at 1:14 am

    #1284982

    Cedric-295411 (2/10/2011)

    When I read the question ,the fact that the answer to the first part is "Yes" and the second part is "No" ,inherently causes one to question the QoD in this case .However I read the question in it's totality , and a Yes && No in this case led me to deduce a "No" ,I have used the command before in SLCMD mode and therefore knew that the correct command is in fact !!DIR

    Leave that as it be , so no questions in the QoD should not be taken literally ? Or some should an some shouldn't ??? (lesson learned in that case !!)

    Steve --> Was a very insightful question ,so no harm done ,just tried to highlight the part that could cause confusion and did in my case

    Thanks all for contributions ,still learned a lot .

    I've got questions wrong where I've "corrected" - often subconciously - a piece of syntax or grammar that was key to the question's answer. It's all part of the rich tapestry of QoD I suppose!

  • February 11, 2011 at 10:57 am

    #1285828

    Good question. I particularly like using :connect when I need to test something on multiple servers and don't feel like changing my connection 10+ times.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • February 12, 2011 at 8:41 am

    #1286025

    This is good timing for that question: I was wondering why on some servers, I don't get intellisense in my query window.

    Now, sqlcmd explains everything

    Thanks for the question (I know how to correct the situation now) and for all the discussions (it helps me understand what is sqlcmd and the differences between it and "query" window)

  • February 13, 2011 at 11:52 pm

    #1286294

    tilew-948340 (2/12/2011)

    This is good timing for that question: I was wondering why on some servers, I don't get intellisense in my query window.

    Now, sqlcmd explains everything

    Thanks for the question (I know how to correct the situation now) and for all the discussions (it helps me understand what is sqlcmd and the differences between it and "query" window)

    This may have nothing to do with why you aren't seeing intellisense on some of your servers but it only works on SQL 2008+ servers. If you connect to a 2000-2005 server it doesn't work even though you are connecting with 2008 tools.

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply