-1

I want to execute sqlcmd command on a remote machine.

When I tried to execute 

sqlcmd -I -b -r -S %server% -d %db% -U %user% -P %PASSWORD% -i '%sql_file%'

I got this error:

sqlcmd : command not found

Is there any way so that I can refer sqlcmd command from internet, as like we do for javascripts? So that it will just invoke it on the fly and execute the SQL script?

Any help will be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ms_27
  • 1,484
  • 15
  • 22
  • 1
    No, you cannot do this - `sqlcmd` is a **tool** that needs to be copied onto that machine where you want to run it. – marc_s Jan 14 '14 at 11:22
  • thanks for quick response.. then how to accomplish that on the fly, because the remote machine will be changing, it won't be constant. Is there any way...?? – ms_27 Jan 14 '14 at 11:28
  • You could write your own `sqlcmd` clone and ship that with your scripts - or make it downloadable with `Click-Once` to be as much "on the fly" as possible – marc_s Jan 14 '14 at 11:30
  • Could you provide few details of how to use Click-Once by command only. I have searched for it, but didn't get much clear idea... – ms_27 Jan 14 '14 at 14:22

1 Answers1

1

sqlcmd is an application, in fact it is sqlcmd.exe. This has to be installed on/copied to a location that can be accessed by the machine that shall run sqlcmd. That can be a local installation on the remote machine, but it can also be a network share that the remote machine can access.

At least sqlcmd does not need to be installed and run on the machine with the SQL server. sqlcmd can run on any client and connect to a remote SQL Server when you provide that server through "-S server" command line switch.

Werner Henze
  • 16,404
  • 12
  • 44
  • 69
  • Yeah, I already tried that way. In my task, I do have `sqlcmd.exe` and `osql.exe` both. I have tried to execute a sql file by invoking both of these exe files. Still, there are some issues. By the way, I am using TeamCity for this purpose. – ms_27 Jan 14 '14 at 14:20
  • *Still, there are some issues.* Do you mind sharing these issues? Without more Information I cannot help you. – Werner Henze Jan 14 '14 at 14:26
  • For osql it is : `Utility\osql.exe -n -I -S -b -r '' -d '' -U '' -P '' -i <.sql file>` error: `[12:10:06][Step 2/4] [DBNETLIB]SQL Server does not exist or access denied. [12:10:06][Step 2/4] [DBNETLIB]ConnectionOpen (Connect()). ` and when I tried to fix this, I found that osql.exe doesn't make clear difference in server/instance. And, hence I switched to sqlcmd. But, sqlcmd gave following error: `[20:36:40][Step 6/6] Applications/.../: line 1: ..\Utility\sqlcmd.exe: command not found [20:36:40][Step 6/6] Process exited with code 127` – ms_27 Jan 14 '14 at 15:09
  • What is the ? A .bat file? What is in line 1 of this file? A call to "..\Utility\sqlcmd.exe"? Then probably you are not in the right path or sqlcmd.exe was not copied to the utility folder. – Werner Henze Jan 14 '14 at 15:20
  • : TeamCity uses its own specific temporary location to store the code/script to be executed. We can say its a bat file. And, yes, line 1 has call to sqlcmd.exe. – ms_27 Jan 14 '14 at 15:27
  • And, sqlcmd is copied at checkout location. I verified it manually. – ms_27 Jan 14 '14 at 15:28
  • TeamCity's message is clear: "..\Utility\sqlcmd.exe" is not found. So check your current drive and Directory. – Werner Henze Jan 14 '14 at 15:33
  • But, have you noted, it is able to find `osql.exe` at same location where `sqlcmd.exe` exists. And, I tried to execute `..\Utility\sqlcmd.exe` for same folder structure on local machine, and it worked. I think it might be due to absence of sql server on remote machine.. – ms_27 Jan 14 '14 at 15:45
  • You wrote Utility\osql.exe and ..\Utility\sqlcmd.exe. Did you check current Directory and path when run from TeamCity as I asked you? – Werner Henze Jan 14 '14 at 15:55
  • As you said, I have checked current directory. And, yes it was there, but TeamCity couldn't locate it. Now, I fixed that and it is able to access it. But, new error is encountered. `[21:47:47][Step 6/6] /Applications/buildAgent/temp/agentTmp/custom_script1667233984163947415: line 1: Utility/sqlcmd.exe: Permission denied [21:47:47][Step 6/6] Process exited with code 126 [21:47:47][Step 6/6] Step Command Line failed` same error is there for osql.exe too. – ms_27 Jan 14 '14 at 16:22