December 11, 2008 at 8:03 am
Anyone have any idea's on this? This code works fine in sql 2005 (on a different but similar server), but in SQL 2008 express, it loops endlessly because it get's file not found when it tries to dir a directory with spaces in it (note: this is just the first part of the script):
declare @cmd varchar(8000),
@CurrentDir varchar(8000),
@CurrentRootDir varchar(8000),
@RootPath varchar(8000),
@Title varchar(8000),
@Keywords varchar(8000),
@Created datetime,
@DamID int,
@FullFile varchar(8000),
@FileNm varchar(8000),
@FileName varchar(8000),
@FileExt varchar(8000),
@SaveToPath varchar(8000),
@AssetPath varchar(8000),
@UseFile bit,
@Cat int
SET NOCOUNTON
Select @RootPath = 'c:\temp\logo1\', @Created=GETDATE()
Create table #tmp (dir varchar(8000))
select @cmd = 'dir /b '+@RootPath
insert into #tmp exec xp_cmdshell @cmd
Declare @dir table (dirs varchar(8000), b bit)
Declare @file table (filep varchar(8000),filen varchar(8000), b bit)
Declare @UsedFiles table(filenm varchar(8000))
Insert into @dir (dirs,b)
select rtrim(ltrim(dir)),0 from #tmp where dir is not null
delete #tmp
While exists(Select * from @dir where b = 0)
Begin
Select @CurrentDir=dirs from @dir where b=0
update @dir set b = 1 where dirs=@CurrentDir
Select @cmd = 'dir /b "'+@RootPath+@CurrentDir+'"'
Insert into #tmp exec xp_cmdshell @cmd
Insert into @dir(dirs,b) Select @CurrentDir+'\'+dir,0 from #tmp where dir not like '%.jpg%' and dir not like '%.eps' and dir not like '%.ai' and dir not like '%.pdf' and dir not like '%.gif' and dir not like '%.png' and dir not like '%.tif' and dir not like '%.tiff' and dir is not null
Insert into @file (filep,filen,b) Select @CurrentDir+'\'+dir,dir,0 from #tmp where dir like '%.jpg%' or dir like '%.eps' or dir like '%.ai' or dir like '%.pdf' or dir like '%.gif' or dir like '%.png' or dir like '%.tif' or dir like '%.tiff'
Delete #tmp
End
December 11, 2008 at 8:30 am
Not sure if this will help but have you tried to wrap the directory in Char(39) or Char(34). You can also test out your command to make sure it works using SQLCMD
December 11, 2008 at 8:32 am
Never mind, I missed a \ in my script on the second server - it's always the little details that trip us up
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply