Running a sqlcmd from Remote Machine

Question

Running a sqlcmd from Remote Machine

mp5387

Right there with Babe

Points: 794
More actions
December 18, 2013 at 4:34 pm

#281407

Hello everybody, I am trying to do this command :
sqlcmd -S servername\instancename,1433 -E -d dbname -i c:\mypath\myscript.sql -o c:\mypath\myoutput.txt
and inside input file I put an easy select query, and this command works on local machine, but when I run this command from a remoe machine, all the time I get bellow error:
sqlcmd: Error: Error occurred while opening or operating on file c:\mypath\myscript.sql ( Reason: The system cannot the path specified ) any help would be appreciated. Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004592 More actions · Answer 1

Which C: drive are you trying to write to? It's not going to write to your local C: Drive from a remote machine. You need to use UNCs to a SHARE instead.

--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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

When you write to the output file using a UNC path, make sure that the account you're running under has permission to write to the network folder you specify. Otherwise, you'll get the dreaded permission error.

Another option is to write it to the local file system (the remote one) where the directory is already shared. Then you'll be able to copy it from there to the calling server's file system.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

mp5387 Right there with Babe Points: 794 More actions · Answer 3

Thanks for your solution, but the error is about my input file , and I think after that it wil go to write the result to output file.

Also, I put my sqlcmd command into a batch file, and I put it on C drive on the server.

To see what is the problem, I did this:

typed \\servername\c$ then it gave me a full access to c drive of server then I run batch file

I used option -E which I think it uses windows authentication user id which havs full access.

I just have sql server on my server, and I do not have sql server on my pc which I want to connect from that to remote server.

Finally I did not understand your another option. Could you explain more about that please?

Thanks,

Steve Jones - SSC Editor SSC Guru Points: 738112 More actions · Answer 4

When you run SQLCMD from your machine, regardless of where the file is located, it uses the paths that are seen from your machine. The thread runs on your CPU, and the c: drive is your local c: drive.

If you want the paths on the remote machine to be used, you'd have to have SQLCMD executed from that machine, meaning logged onto the server or the equivalent. You could do this with a SQL Agent job, or with a utility like psexec (from sysinternals : http://technet.microsoft.com/en-us/sysinternals/bb545027.aspx)

mp5387 Right there with Babe Points: 794 More actions · Answer 5

Tahnks Steve, I installed Psexec on my emore server and my pc, and I changed my command in my batch file like this:

psexec \\servername -E cmd.EXE /c "sqlcmd -S s01-techsql1 /E -d dbname -i c:\myfolder\myscript.sql -o c:\mypath\output.sql" now the command will run on the remote server, but still I do not have access to folder. I think I have account issue.

When I type in my pc command line \\servername\c$ I can connect to server with my domain user. Also /E in the command means windows authentication. In addition I shared c:\myfolder on the remote server with my domain user, but still I get same error:

sqlcmd: Error: Error occurred because of pening or operating of c:\myfolder .....

I will work more on this and let you know the result. Thanks for help

Steve Jones - SSC Editor SSC Guru Points: 738112 More actions · Answer 6

You are welcome. It does sound like an account/security issue.

Good luck. Let us know what you find out.

mp5387 Right there with Babe Points: 794 More actions · Answer 7

This command works on server when I run it from my pc:

psexec \\servername -E cmd.EXE /c "sqlcmd -S s01-techsql1 -E -d dbname -i c:\myfolder\myscript.sql -o c:\mypath\output.txt"

Now I changed the command like the bellow command to change the variable in where clause each time:

psexec \\servername -E cmd.EXE /c "sqlcmd -S s01-techsql1 /E -d dbname -v entertext="'%anything%'" -i c:\myfolder\myscript.sql -o c:\mypath\output.sql"

myscript.sql is like:

select * from table_name

where summary like $(entertext);

this command works just for variables with single and exact word. It does not work for variables which has space between them or when I enter part of summary .

In fact, I wanted to write something which user be able to enter variable each time when he/she run this exe file( like this in oracle: select * from table_name where summary = &entertext; I was looking the same command in sql server , but I did not find). I do not know .net or any other programing language( I know just csh and command line). I want to do this just with sql command.

Could you help me PLEASE??????? Thank you

mp5387 Right there with Babe Points: 794 More actions · Answer 8

I found when I run this sqcmd commend in my command prompt it workscorrectly , but when I run it from batch file it does not work. Can some body tell me why this command does not from batch file? Thanks MP

Running a sqlcmd from Remote Machine

Cookies on SQLServerCentral