Tuesday, March 20, 2012

Path spaces using xp_cmdshelll

I know this is probably a DOS question but does anyone know how the following would work without taking out the spaces in the folder "Audit Tax Planning\" (works fine in paths without spaces)?

Declare @.ren varchar(255)
Declare @.path varchar(100)

Set @.path = '\\main\WGDATA\Accounting\Audit Tax Planning\' --G:\Accounting\Audit Tax Planning\05BE

IF EXISTS(SELECT id FROM tempdb..sysobjects WHERE id = object_ID('tempdb..#Tax_File'))
DROP TABLE #Tax_File

CREATE TABLE [#Tax_File](
[File_Name] varchar(50),
)

Set @.ren = 'dir /B ' + @.path + 'test.txt'

Insert into #Tax_File ([File_Name])
EXEC master..xp_cmdshell @.ren

If not exists(select [File_Name]
from #Tax_File
where right([File_Name],4) = '.txt')
begin
RAISERROR ('File does not exist.', 16, 1)
Return
end

select * from #Tax_FileSure, you need quotes

Read here

http://weblogs.sqlteam.com/brettk/archive/2005/06/28/6895.aspx|||Yup, the quotes inside work, thank you.

No comments:

Post a Comment