Help with xp_fileexist 1

[Risk]

Hello

I am using the xp_fileexist command inside a stored proc to check to see if files that are created each night exist in a directory (hence check to see if they where created properly). Is there any way of outputting the result of the xp_fileexist command to a text file or even to a table.

i.e

EXEC master..xp_fileexist 'C:\Test\test.txt' brings back

File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1 1 1

How would I get this output in a text file from within a Stored Proc?

Thanks

 

[clapag22]

create table FileExist (DateStamp datetime default getdate(), FileExists bit, FileIsADirectory bit, ParentDirectoryExists bit)
GO

insert into FileExist (FileExists, FileIsADirectory, ParentDirectoryExists)
EXEC master..xp_fileexist 'C:\Test\test.txt'
GO

select *
from FileExist
GO

Craig

 

[ClayG]

If you only really want the main "file exists" flag, you can call xp_fileexists with a second parameter - I think its zero or 1 - that suppresses the resultset, and instead you get the return status from the call:

exec @exists = master..xp_fileexists 'c:\test.txt', 0

then you can stick the value wherever you like

 

[Risk]

Thanks Graig

one more question, if I wanted to add a column to the fileexist table to include the actual file name (that was held in a variable), how could that be included in the insert statement?

i.e

insert into FileExist (Filename, FileExists, FileIsADirectory, ParentDirectoryExists)
@Filename, EXEC master..xp_fileexist 'C:\Test\test.txt'

Does not work. Can this be done?

Thanks again.

 

[TysonLPrice]

Is there anything similar to EXEC master..xp_fileexist that will list all the files in a particular folder?

 

[clapag22]

Risk-

No, that wouldn't work. But you could do something like this:

create table FileExist (DateStamp datetime default getdate(), FileExists bit, FileIsADirectory bit, ParentDirectoryExists bit, FileName varchar(50))
GO

declare @filename varchar(50)

select @filename = 'C:\Test\test.txt'

insert into FileExist (FileExists, FileIsADirectory, ParentDirectoryExists)
EXEC master..xp_fileexist @filename 
GO

update FileExist 
set FileName = @filename
where FileName is null
GO

select *
from FileExist
GO

As long as you run this the same way all the time there will always be only one null filename and the update statement will update that one row.

 

[Risk]

Thanks again Craig , that worked a treat mate.

Oh and BTW, I apologise for calling you Graig in my earlier post!

Risk