Microsoft SQL xp_cmdshell doesn't like filenames with spaces. Can I replace the space with something else?

Question

I have this TSQL code that dumps data from tables using BCP. It looks complicated, but it simply creates a @command string to be executed once for each table, then BCP dump the table records to disk. It's a nice way to backup all the table data quickly. Below I show the resolved version which is a little easier to read.

set @command = 
  'if (''?'' <> ''[dbo].[sysdiagrams]'') 
   BEGIN;
       create table #result (result nvarchar(2048) null );
       declare @temp nvarchar(1000); 
       set @temp = ''' +  @bcpPath + ' ' + @database + '.dbo.'' + 
           substring( ''?'', 8, len(''?'')- 8) +
           '' out "' + @driveLetter + @drivePath +
           '\'' + substring( ''?'', 8, len(''?'')- 8) + 
           ''.out" -c -x -t"|" -Uuser -Ppassword'';
       insert into #result (result)
       exec xp_cmdshell @temp;
       drop table #result;
   END;'
   exec sp_msforeachtable @command

the @bcppath is C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe which has a space.

Without using double quotes around the path "", it gives an error of 'C:\Program' is not recognized... With using double quotes, it gives the same error. With using double double quotes "" "", it says The filename, directory name, or volume label syntax is incorrect.

@command resolves to this when printed:

if ('?' <> '[dbo].[sysdiagrams]') 
BEGIN;
    create table #result (result nvarchar(2048) null );
    declare @temp nvarchar(1000); 
    set @temp = '"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" 
        myDB.dbo.' + 
        substring( '?', 8, len('?')- 8) +
        ' out "E:\DataExports\' + 
        substring( '?', 8, len('?')- 8) + '.out" -c -x -t"|" -Uuser -Ppassword';
    insert into #result (result)
    exec xp_cmdshell @temp;
    drop table #result;
END;

EDIT:

Oddly, I put an ECHO ? && in front of the "path" and it worked (surrounded by double quotes.) .... Why?

Answer 1

You have to put something before quoted path to avoid error C:\Program' is not recognized... so I used CALL statement and it worked for me ...

declare @cmd nvarchar(1000)

set @cmd = 'call "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" myDB.dbo.'
exec xp_cmdshell @cmd