SQLCMD Mode

Question

SQLCMD Mode

Mike Scalise

SSCrazy Eights

Points: 8145
More actions
April 26, 2019 at 6:21 pm

#3541752

Hi,
I'm just curious. I know when we as database administrators and developers install SSMS on a new machine, we either import or set our preferences. I know there's 10 or so that I set. One thing I'm curious about is whether you enable SQLCMD Mode to be on by default. Having it on should still allow all of the T-SQL commands/code you've always had, but it also allows you to connect to other SQL Server instances within the same query window. So while I've never set this feature to be on at all times, I'm considering adding it to my list of ones to permanently enable.
Does anyone enable it? Any reason not to?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alejandro Santana SSCommitted Points: 1835 More actions · Answer 1

Hey,

I only enable it to check SQL Server logs across different servers and checking the msdb backup tables, i know you could use the Registered Server option for that or automatic daily report emails (even we have those), but i like to go server per server through SSMS even if there are not emails regarding issues with the instance or the databases.

apart from what i said before, never had the idea to use it for anything else.

Greetings.

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 2

I enable it rarely. We do use it for deployments from Redgate products, but overall I think I worry about accidentally doing something strange in a script. Also, since I share code with others, most don't enable it, so I don't want it on by default.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

What I'd like to know is how to enable the functionality of the SQLCMD mode (not the other SQLCmd as in .exe) functionality in a stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 4

All,

Thanks for your responses! Jeff, if I'm understanding your comment correctly, I'm not sure there's a way to do that...

https://dba.stackexchange.com/questions/5468/can-i-enable-sqlcmd-mode-from-inside-a-script

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 5

I don't use it a lot - but I do have several scripts where it is very useful. For example - creating logins/users in an AOAG environment. The script creates the login and user on the primary - then creates the login on the secondary with the same SID and optionally removes/disables the login on the primary (for user access to the read-only secondary).

I have also used it to perform quick and dirty comparisons between environments - as well as extract data to files for further data comparisons.

What I would really like is for SSMS to recognize that a script is written with SQLCMD mode enabled.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

Mike Scalise wrote:

All, Thanks for your responses!
Jeff, if I'm understanding your comment correctly, I'm not sure there's a way to do that...
https://dba.stackexchange.com/questions/5468/can-i-enable-sqlcmd-mode-from-inside-a-script Mike

I was pretty sure that was true but you never know who knows an "oolie". Thanks for the confirmation, Mike.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)