Jeff,

What I was trying to do was find a means of getting a file listing so I could scedule a job to delete files that were say two weeks or more old. So because of your assistance, I was able to come up with this stored procedure that basically recreates the "dir" command to a table.

Now that's acting stable, I can now move on to making a procedure that references this one to delete files based on date, size, name, or whatever.

Thanks again and have a great weekend!

Tony

create procedure [dbo].[sproc_utility_Dir]

(

@pathvarChar ( 1000 ) = 'c:'

)

as

begin

declare

@isDirectorybit,

@namevarChar ( 0255 ),

@pathFilevarChar ( 1255 ),

@commandnvarChar ( 4000 ),

@resultint

set nocount on

create table

#commandLineOutput

(

rowvarChar ( 0400 ),

flagbit

)

create table #detail

(

[isDirectory]bit,

[path]varChar ( 1000 ),

[name]varChar ( 0255 ),

[pathFile]varChar ( 1255 ),

[Alternate Name]varChar ( 0255 ),

[Size]int,

[Creation Date]varChar ( 0010 ),

[Creation Time]varChar ( 0010 ),

[Last Written Date]varChar ( 0010 ),

[Last Written Time]varChar ( 0010 ),

[Last Accessed Date]varChar ( 0010 ),

[Last Accessed Time]varChar ( 0010 ),

[Attributes]int

)

select

@command = 'dir "' + @path + '\*.*" /b /a:d /o:n',

@result = 0

insert into

#commandLineOutput

(

row

)

execute@result = master.dbo.xp_cmdshell

@command

if (@result <> 0)

begin

select

[isDirectory],

[path],

[name],

[pathFile],

[Alternate Name],

[Size],

[Creation Date],

[Creation Time],

[Last Written Date],

[Last Written Time],

[Last Accessed Date],

[Last Accessed Time],

[Attributes]

from

#detail

where

0 > 1

drop table#commandLineOutput

drop table#detail

return@result

end

update

#commandLineOutput

set

flag = 1

where

flag is null

select

@command = 'dir "' + @path + '\*.*" /b /a:-d /o:n',

@result = 0

insert into

#commandLineOutput

(

row

)

execute@result = master.dbo.xp_cmdshell

@command

if (@result <> 0)

begin

select

[isDirectory],

[path],

[name],

[pathFile],

[Alternate Name],

[Size],

[Creation Date],

[Creation Time],

[Last Written Date],

[Last Written Time],

[Last Accessed Date],

[Last Accessed Time],

[Attributes]

from

#detail

where

0 > 1

drop table#commandLineOutput

drop table#detail

return@result

end

update

#commandLineOutput

set

flag = 0

where

flag is null

delete

from

#commandLineOutput

where

row is null

declareloopingcursor

forselect[flag],

[row]

from#commandLineOutput

openlooping

fetch next fromlooping

into @isDirectory,

@name

while (@@fetch_status = 0)

begin

select@pathFile = @path + '\' + @name

insert into #detail

(

[Alternate Name],

[Size],

[Creation Date],

[Creation Time],

[Last Written Date],

[Last Written Time],

[Last Accessed Date],

[Last Accessed Time],

[Attributes]

)

execute @result = master.dbo.xp_GetFileDetails

@pathFile

if (@result = 0)

begin

if (@isDirectory = 0)

begin

update

#detail

set

[isDirectory] = @isDirectory,

[path] = @path,

[name] = @name,

[pathFile] = @pathFile,

[Creation Time] = replicate('0', 6 - len([Creation Time])) + [Creation Time],

[Last Written Time] = replicate('0', 6 - len([Last Written Time])) + [Last Written Time],

[Last Accessed Time] = replicate('0', 6 - len([Last Accessed Time])) + [Last Accessed Time]

where

[isDirectory] is null

and[path] is null

and[name] is null

and[pathFile] is null

end

else if (@isDirectory = 1)

begin

update

#detail

set

[isDirectory] = @isDirectory,

[path] = @path,

[name] = @name,

[pathFile] = @pathFile + '\',

[Creation Time] = replicate('0', 6 - len([Creation Time])) + [Creation Time],

[Last Written Time] = replicate('0', 6 - len([Last Written Time])) + [Last Written Time],

[Last Accessed Time] = replicate('0', 6 - len([Last Accessed Time])) + [Last Accessed Time]

where

[isDirectory] is null

and[path] is null

and[name] is null

and[pathFile] is null

end

end

fetch next fromlooping

into @isDirectory,

@name

end

closelooping

deallocatelooping

select

[isDirectory],

[path],

[name],

[pathFile],

[Alternate Name],

[Size],

convert(varChar ( 10 ), cast([Creation Date] as dateTime), 101) as [Creation Date],

subString([Creation Time], 1, 2) + ':' + subString([Creation Time], 3, 2) + ':' + subString([Creation Time], 5, 2) as [Creation Time],

convert(varChar ( 10 ), cast([Last Written Date] as dateTime), 101) as [Last Written Date],

subString([Last Written Time], 1, 2) + ':' + subString([Last Written Time], 3, 2) + ':' + subString([Last Written Time], 5, 2) as [Last Written Time],

convert(varChar ( 10 ), cast([Last Accessed Date] as dateTime), 101) as [Last Accessed Date],

subString([Last Accessed Time], 1, 2) + ':' + subString([Last Accessed Time], 3, 2) + ':' + subString([Last Accessed Time], 5, 2) as [Last Accessed Time],

[Attributes]

from

#detail

order by

[isDirectory] desc,

name asc

drop table#commandLineOutput

drop table#detail

end