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?

Possibly. If xp_cmdshell calls bcp without the path component a la "bcp myDB.dbo...", what happens? I ask b/c we use bcp for bulk loading & it doesn't care if the file path has spaces in it in our local or client installations. — DaveE, Apr 06 '11 at 16:43
I'd really like to know why Echo ? && pre-pended fixed it... — Zachary Scott, Apr 13 '11 at 21:44
`xp_cmdshell` can take multiple pairs of quotes, just not in the first token. See my answer here: http://stackoverflow.com/questions/3759331/escaping-command-parameters-passed-to-xp-cmdshell-to-dtexec/19011299#19011299 — Ben, Sep 26 '13 at 09:09

score 8 · Accepted Answer · answered Jan 22 '16 at 01:49

8

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

answered Jan 22 '16 at 01:49

hoggar

3,699
5
31
41

1

No need to append CALL to the command line argument. Just having the path to the file in double quotes would suffice. So this should work: '"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" myDB.dbo.' – umbersar Mar 17 '18 at 06:05

score 5 · Answer 2 · answered Apr 06 '11 at 16:49

5

Try specifying the short name for the parts of the path containing spaces For example, PROGRA~1 rather than Program Files. So, your first path would be something like C:\PROGRA~1\MI6841~1\90\Tools\Binn\bcp.exe. If you don't have any spaces, you should be able to drop the quotes.

If you perform a dir /x in the directory containing the long directory/file names you can obtain the short 8.3 name.

answered Apr 06 '11 at 16:49

arcain

14,920
6
55
75

+1, but it depends on the current system, at the next system the short name can be different – jeb Apr 06 '11 at 19:22
Absolutely, however (interestingly) a co-worker checked this on his machine, and the short names were identical. I would have thought they would have to be different, especially *Microsoft SQL Server => MI6841~1* since we have wildly different software installs. – arcain Apr 06 '11 at 19:27
I suppose it only depends on the order of installing, as the short name is stored (also the long name) in the moment of creation – jeb Apr 06 '11 at 19:33
1

Hey check this out (from [Wikipedia](http://en.wikipedia.org/wiki/8.3_filename)) - #4 Beginning with Windows 2000, if at least 4 files or folders already exist with the same initial 6 characters in their short names, the stripped LFN is instead truncated to the first 2 letters of the basename (or 1 if the basename has only 1 letter), followed by 4 hexadecimal digits derived from an undocumented hash of the filename, followed by a tilde, followed by a single digit, followed by a period ".", followed by the first 3 characters of the extension. You learn something new everyday. – arcain Apr 06 '11 at 19:39
I didn't know that, and I add a long filename support for a fat-driver on an embedded system some time ago (but only for reading). – jeb Apr 06 '11 at 19:51
It's a good idea, and that's what I started with, but I ran in to the same problem where different systems had different paths to the same locations. – Zachary Scott Apr 13 '11 at 21:41
`xp_cmdshell` can take multiple pairs of quotes, just not in the first token. See my answer here: http://stackoverflow.com/questions/3759331/escaping-command-parameters-passed-to-xp-cmdshell-to-dtexec/19011299#19011299 – Ben Sep 26 '13 at 09:10

score 1 · Answer 3 · answered Apr 06 '11 at 19:16

As workaround, you could use subst.

subst p: "C:\Program Files\Microsoft SQL Server\"

so you didn't need the spaced path anymore.

Or you try to find out why it fails.

exec xp_cmdshell 'cmd /c echo %cmdcmdline% "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe"'

The cmdcmdline should show you the complete command, if the quotes are already there this should work

exec xp_cmdshell 'cmd /c "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" <the rest of your command>'

score 0 · Answer 4 · answered Nov 14 '17 at 05:49

0

This weird construction also working:

exec xp_cmdshell '""%ProgramFiles%\WinRAR\"rar.exe a -v20M "C:\test\test.rar" "C:\test\data\""'

answered Nov 14 '17 at 05:49

shmnff

647
2
15
31

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

4 Answers4