copy-item using xp_cmdshell in SQL Server

Question

I'm trying to send the "copy-item" command using xp_cmdshell to move an item I encrypt earlier in a sproc. In the sproc I create a .bat file, executed as a scheduled task, to do the encryption.

I've tried using the same method to execute the copy-item command, as well as a basic EXEC xp_cmdshell command and neither is working. Any idea why it would work to exec gpg, but not when i try to exec the copy-item command?

Here's what I've tried:

set @cmd = 'copy-file c:\MissionControlDocs\'+@docFolder+@rawfilenamewoext+'.txt.gpg y:\aep-ohio>> ' + @batchFileName)
exec master..xp_cmdshell @cmd, no_output

and selecting the command into a batch file, then calling the task (which runs successfully on the server):

select @cmd = rtrim('echo copy-file c:\MissionControlDocs\'+@docFolder+@rawfilenamewoext+'.txt.gpg y:\aep-ohio>> ' + @batchFileName)
exec master..xp_cmdshell @cmd, no_output       

Copy-file works when I run it directly in the powershell application on the server - no matter what directory I'm currently in.

Your thoughts are greatly appreciated.

Answer 1

Found an answer, in 2 parts.

First, I was using copy-item (which is really powershell, not cmdshell).

Second, I was able to add the copy line into my .bat file, by adding START /wait to the previous line so that my copy line didn't execute until the previous command completed.