Problem with xp_cmdshell and spaces in file path

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with xp_cmdshell and spaces in file path

Author

Topic

dhjackal
Starting Member

42 Posts

Posted - 2009-01-23 : 10:01:02

Hello all,

I'm having a problem getting xp_cmdshell to work on file paths with spaces in them :o(

This works ;

EXEC master.dbo.xp_cmdshell 'dir \\server\temp\'

This doesn't

EXEC master.dbo.xp_cmdshell 'dir \\server\temp dir\'

I've tried the following
1) Double quoting the entire string e.g '"dir \\server\temp dir\"'
2) Single quoting the spaces e.g 'dir \\server\temp''dir\'
3) Concatenating the ascii for space into the string e.g 'dir \\server\temp'+CHAR(32)+'dir\'

And other weird and wild combanations but all to no avail

Has anyone been able to fix this problem?

Any help appreciated

Declan

mfemenel
Professor Frink

1421 Posts

Posted - 2009-01-23 : 13:10:32

Ah, so close. The quotes need to go around the command that DIR will run...like this:
declare @cmdline varchar(2000)
set @cmdline='dir "C:\Program Files\"'
exec master.dbo.xp_cmdshell @cmdline

Mike
"oh, that monkey is going to pay"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-23 : 14:35:48

You have to give it exactly the same command you would use from a command window:

dir "\\server\temp dir\"

CODO ERGO SUM

dhjackal
Starting Member

42 Posts

Posted - 2009-01-26 : 06:19:40

Thanks guys I got it working with the following

SET @DatabaseName = 'TestDatabase'
SET @FilePath = '"\\server\directory\' + @DatabaseName + '\'
SET @FileMask = 'bak'
SET @CmdString = 'dir ' + @FilePath + '*.' + @FileMask + '"'

exec master.dbo.xp_cmdshell @cmdline

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources