How to transfer files at remote location using sql server job?

Question

I have a job configured in which it transfers the file at remote location.
My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on failure unknown user name or bad password.
I am not able to find out the exact reason that why this file is not being transferred.

If there is an error in my code then it should give the same error from the command prompt. Can anyone help me understand why I am getting the error?

Answer 1

that's probably because the user trying to move the files is the user that is running the job (probably the user that SQL Server agent is configured to run). Try giving read permission to this user on the folder you are reading the files from and write on the folder you are writing the files.

Answer 2

The issue has been solved . As the remote location folder was shared and was accessible to every one. My command was working fine from command prompt , even any user was able to create their own file on that location and also able to delete the file from that location.

The issue was related to user. My job was being executed by servername\administrator and remote location administrator password was changed due to that bad password error occurred. I told my IT Team about the problem and they reset the server password as older one, and my job began to work fine.

The issue was solved.

I just want to know how my sql job authenticates the server login as I go through the script of my job and found nothing helpful regarding authentication.

Can any one explain it to me.

Thanks

Nitesh Kumar

Answer 3

the main reason for failures from batch files with something that works from the command line is the % signs.

On the command line, a single % is required, so for %a in (*.exe) do echo %a works fine, but if you wanted that same command in a batch file, you have to double the % signs, which makes that command now for %%a in (*.exe) do echo %%a

This is only for those that you calculate yourself - using variables like %TEMP% would not change.