Microsoft SQL xp_cmdshell doesn't like filenames with spaces. Can I replace the space with something else?
Solution 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
Solution 2
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.
Solution 3
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>'
Zachary Scott
#SOreadytohelp Recommended books: Agile Principles, Patterns, and Practices in C# Applying Domain-Driven Design and Patterns Patterns of Enterprise Application Architecture Design Patterns (GOF) Refactoring: Improving the Design of Existing Code Test Driven Development: By Example Implementation Patterns Domain-Driven Design (Evans)
Updated on June 20, 2022Comments
-
Zachary Scott about 2 years
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
isC:\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 saysThe 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? -
jeb about 13 years+1, but it depends on the current system, at the next system the short name can be different
-
arcain about 13 yearsAbsolutely, 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.
-
jeb about 13 yearsI suppose it only depends on the order of installing, as the short name is stored (also the long name) in the moment of creation
-
arcain about 13 yearsHey check this out (from Wikipedia) - #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.
-
jeb about 13 yearsI 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).
-
Zachary Scott about 13 yearsIt'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.
-
Ben almost 11 years
xp_cmdshell
can take multiple pairs of quotes, just not in the first token. See my answer here: stackoverflow.com/questions/3759331/… -
umbersar over 6 yearsNo 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.'