Search code examples
sqlsql-serversql-agent-jobxp-cmdshell

SQL xp_cmdshell copy files between servers

I am trying to move all .zip in a specific folder to another folder. the source folder is located on another server, currently i am using

EXECUTE xp_cmdshell 'copy \\server1\e$\ETL\*.zip \\server2\e$\ETL\'
GO

Which is working if I am logged into both server, but the goal is to automate this process VIA sql server job agent. I have tried

EXECUTE sp_xp_cmdshell_proxy_account 'domain\useracc','pass'
GO
EXECUTE xp_cmdshell 'copy \\server1\e$\ETL\*.zip \\server2\e$\ETL\'
GO

but I am receiving the following error;

An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.

And also not sure if this is my solution. Please help with how I can achieve this. The file names on server1 change name and quantity everyday.

Solution

  • I Found that the following worked for me;

    1. In the command prompt, type services.msc, this would open the list of all services on the server.

    2. In the list of services, look for SQL Server Agent, Right Click -> Properties. Go to Logon Tab

    Change the logon to a user with access on both servers. then re-write your script to use Server Agent CmdExec job steps(Thank you Pete Carter)