question

declare @cmdstring varchar(1000) set @cmdstring = 'copy \\snap4\share1\xx\GMProductionReports(2).csv \\snap4\share1\xx\GMProductionReports(2)'+replace(convert(nvarchar(50), getdate(), 120), ' ',':')+'.csv' exec master..xp_cmdshell @cmdstring i am getting the below response when i execute The filename, directory name, or volume label syntax is incorrect.

I realize that this is an old post but I didn't see an answer for what the OP actually wanted done. Considering that the OP responded to the PowerShell answer with an "I don't even know where to begin", I betting that a moderator marked it as the accepted answer and, with apologies to the author of that answer, I wish the moderators would stop marking answers just to have one. As for me, even if I had to do this through PowerShell, I wouldn't do it through a PowerShell prompt nor a Windows Scheduler job. As a "hybrid" DBA (combination System and Application DBA) as well as a Developer, I have more than enough to do without spreading my work across hell's half acre. I want it all in one spot so I can monitor it and that spot is the SQL Server Agent. ;-) It's bloody well bad enough that I have Web Methods running against my databases (not my choice, either). To wit, I strongly disagree with the moderators marking that as the accepted answer because I actually wouldn't tolerate that in my shop. ;-) On to the problem at hand... even though it's an old post... > declare @cmdstring varchar(1000) > > set @cmdstring = 'copy \snap4\share1\xx\GMProductionReports(2).csv \snap4\share1\xx\GMProductionReports(2)'+replace(convert(nvarchar(50), getdate(), 120), ' ',':')+'.csv' > exec master..xp_cmdshell @cmdstring That command might not even work from a CMD (DOS) window unless the current drive just happened to be the right one because it's missing the drive letter. Never trust defaults or the DOS "PATH" environment variable for such an important thing. It can and will make it necessary for you to quickly update your resume. ;-) That's something else that you have to understand. Any drive letters that you use must be on the SQL Server itself. If it’s anywhere else, you need to use the full UNC even if you’ve mapped a drive letter (unless it’s a drive letter that SQL Server has been assigned to attach as a resource). The UNC can be a bit tricky, as well. It has to be a “share” that the login for the SQL Server Service or the SQL Server Agent Service can actually “see” and has privs to. Further, as Dave Green already identified, you cannot use colons in your file names, period. It’s just not allowed because colons are reserved separators for drive letters or for labels in batch files (some use them for remarks... like me). Last but not least, I think it’s a waste of time to convert DOS commands to NVARCHAR() in most cases. That might only be because I’ve not had the pleasure of working in a CMD window where English wasn’t the assigned language. All of that brings us to a code example. If the name of the machine that the file is on is “SomeMachine” and the name of the share on that machine is “SomeShare”, then your code needs to look like the following… DECLARE @CMD VARCHAR(8000) ; SELECT @CMD = 'copy ' + '\\SomeMachine\SomeShare\snap4\share1\xx\GMProductionReports(2).csv ' + '\\SomeMachine\SomeShare\snap4\share1\xx\GMProductionReports(2)_' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19),GETDATE(),120),':',''),' ','_'),'-','') + '.csv' ; EXEC xp_cmdshell @CMD ; Personally, I wouldn't do this copy. I'd simply do a file rename to prevent the duplication of data.