3

I've run into a problem about sqlcmd,

In the computer there is SSMS 2017 and I can use SQLCMD MODE in SSMS 2017, but I can't call sqlcmd in command prompt,

"'sqlcmd.exe' is not Recognized as an Internal or External Command, Operable Program or Batch File"

and i couldn't find the SQLCMD.EXE in the whole computer.

Since i can use SQLCMD MODE in SSMS, Is this means, there must be a sqlcmd.exe in the computer or not?

Thank you very much,

Chen J
  • 57
  • 1
  • 1
  • 6
  • 1
    Also, sqlcmd mode is quite different from `sqlcmd`. – Thom A Dec 04 '19 at 13:55
  • 1
    @Larnu: you can use `sqlcmd.exe` just as well, there is nothing wrong with adding the `.exe` suffix –  Dec 04 '19 at 13:56
  • 2
    Management Studio's SQLCMD mode does not run `sqlcmd` in the background, it just behaves in a compatible way. `sqlcmd` can normally be found at `C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\\Tools\Binn\SQLCMD.EXE`; presumably you can exclude it from setup by unticking some box, though I don't know precisely which one, as it's included by default and few people should have motivation to change that. Nevertheless, it's not required for a working installation of SQL Server, so yeah. – Jeroen Mostert Dec 04 '19 at 13:56
  • Too used to using bash now, @a_horse_with_no_name (where `sqlcmd.exe` would be invalid). Removed the closing statement. – Thom A Dec 04 '19 at 13:56
  • @JeroenMostert: is `sqlcmd` installed together with SQL Server or with SSMS? If it's part of the SQL Server installation and Chen only has SSMS this could explain it –  Dec 04 '19 at 13:57
  • I *thought* `sqlcmd` was installed with either, @a_horse_with_no_name . Certainly my Work PC has it installed, and it doesn't have SQL server installed; and I don't ever recall downloading it manually. – Thom A Dec 04 '19 at 13:57
  • 1
    @a_horse_with_no_name: I'm going to have to go with "ummm, eh, dunno". The newest versions of SSMS are entirely standalone (and I don't think they include `sqlcmd`), in older versions everything was part of the SQL Server setup one way or another, but with optional parts. All this is assuming Windows, on Linux it's yet another story. One way or another it's definitely possible to end up with an installation that has no `sqlcmd` -- I mean, even with integrated setup you can always delete it manually if you're silly enough. It's just another client tool. – Jeroen Mostert Dec 04 '19 at 13:59
  • I'd say that it's a more than a safe assumption that it's Windows, @JeroenMostert , seeing as there is no SSMS on Linux (still). – Thom A Dec 04 '19 at 14:01
  • @Larnu: this question, certainly; I was addressing the situation in general. – Jeroen Mostert Dec 04 '19 at 14:01
  • @ChenJ, *have* you installed `sqlcmd` to begin with? Even if you have, is it in the path? The command-line utilites [can be installed by themselves](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15). They aren't phased out, they're necessary for executing deployment and migration scripts. What's been phased out are the ancient `osql` and `isql` tools – Panagiotis Kanavos Dec 04 '19 at 14:04
  • @PanagiotisKanavos, Thank you. I'm not sure about what happened during the installation... I want to be sure that there is no sqlcmd installed in the server, so I can ask the person in charge to do it. – Chen J Dec 04 '19 at 14:20

3 Answers3

5

Since i can use SQLCMD MODE in SSMS, Is this means, there must be a sqlcmd.exe in the computer or not?

Apparently not. I just tested by running

:!! powershell pause

in SQLCMD mode in SSMS and examining the process tree in Sysinternals Process Explorer:

enter image description here

It looks like SSMS is using PowerShell's Invoke-SqlCmd which is a seperate SQLCMD-compatible interpreter.

And SQLCMD is a seperate download since SSMS 18.0:

The following tools are no longer installed with SSMS:
OSQL.EXE
DReplay.exe
SQLdiag.exe
SSBDiagnose.exe
bcp.exe
sqlcmd.exe

Deprecated and removed features in 18.0

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks David, it's what i want to know. – Chen J Dec 04 '19 at 14:24
  • Once you've installed the separate download, `sqlcmd` will be at e.g. `C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe` or similar – rakslice May 03 '23 at 20:30
2

Do you try to add your SQL SERVER path to the PATH's environnement variable ?

To check it, run the folowing command on cmd :

Path

Path exemple

That'll print you the variable path. If there is not something like this :

;C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\

;C:\Program Files\Microsoft SQL Server\140\Tools\Binn\

;C:\Program Files\Microsoft SQL Server\140\DTS\Binn\

You won't be able to execute sqlcmd.

SQLCMD is very useful for runing command on multiple instance for example in a batch file. Something cool with this command is also that you can pass in the argument '-i' a sql script to execute and in '-o' a file where output the result. Then, you'll be able to add variable argument with '-v' option.

That's a very powerfull feature and i don't know why everybdoy in the comment saying the opposite.

(sorry for my english, again, i'm french)

vincent PHILIPPE
  • 975
  • 11
  • 26
  • 1
    Merci vincent, exactement, je l'utilise pour exécuter mon sql script. Je ne trouve que" ;C:\Program Files\Microsoft SQL Server (X86)\150\DTS\Binn\" par taper "Path". Mais en fait cette répertoire n'existe pas dans le PC. J'ai que un dossier "License Termes" sous "C:\Program Files\Microsoft SQL Server\150". – Chen J Dec 04 '19 at 14:13
1

My SQLCMD.EXE is located here:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE

You can download it here:

https://msdn.microsoft.com/de-de/library/ms162773.aspx

or

https://learn.microsoft.com/de-de/sql/tools/sqlcmd-utility?redirectedfrom=MSDN&view=sql-server-ver15

DaGloa
  • 11
  • 1