SQL CMD

Question

SQL CMD

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

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

UMG Developer SSChampion Points: 13482 More actions · Answer 3

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

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.

rVadim Hall of Fame Points: 3969 More actions · Answer 5

Did you switch to CMD mode?

From menu: Query -> SQLCMD Mode

--Vadim R.

rVadim Hall of Fame Points: 3969 More actions · Answer 6

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

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/

UMG Developer SSChampion Points: 13482 More actions · Answer 9

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.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 10

Hardy21

SSCrazy Eights

Points: 9708

February 9, 2011 at 4:22 am

#1284323

Good question. Tx

Thanks

Cedz Mr or Mrs. 500 Points: 502 More actions · Answer 11

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 .

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 12

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!

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 13

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]

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 14

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)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 15

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]