SQL Server – How to Write to a Folder with Spaces

command linesql serverssis

I've got a bunch of simple SSIS packages that output SQL data to excel, then I rename the files and move them out to the end user.

Some of them use file paths with spaces in them, and they aren't excited about using new folders. Here's the code I'm using:

    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

but it wouldnt work without the underscores.

Question how can I make this work without underscores?

Best Answer

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.

Related Question