How do you write to a folder with spaces in it?
You should (pretty much always) enclose paths inside quotation marks. So, like this:
declare @date varchar(25),
@sql varchar(1000);
set @date = cast(datepart(month, current_timestamp) as varchar(2)) + ' ' + cast(datepart(day, current_timestamp) as varchar(2)) + ' ' + cast(datepart(year, current_timestamp) as varchar(4));
print @date;
set @sql = 'copy "D:\Data\ED_72.xlsx" "\\ehsintra3\ED 72 weekly\ED 72 ' + @date + '.xlsx"';
print @sql;
exec xp_cmdshell @sql;
Putting paths inside quotation marks ensures the command-processor can understand where a path starts and ends.
Also, instead of using copy
you probably want to look at either robocopy
or xcopy
. The copy
command is brutally weak and frail.
Also, a minor note, add semi-colons to the end of your statements in T-SQL.
The other approach is to use the File System Task within SSIS itself. It handles "complex" path and file names without the rigmarole and risks of xp_cmdshell
escaping.
What risk? There's a limit on double quoted arguments.
See also Escaping command parameters passed to xp_cmdshell to dtexec on Stack Overflow.