Check for file existance with TSQL 2

[mutley1]

Hi all,

I want to create a stored procedure that would check a server for the existance of a file (blah.bin), and if it does exist, use xp_sendmail to notify users.

I guess this can be done using VB and DTS but would prefer to use a stored proc.

The file is on a non-SQL server and could exist in a few different folders (3 folders, each with multiple subdirectories).

Server BLAH\D:\Indexes\Db1\DB\<any number of subdierctories>\file.bin

Server BLAH\D:\Indexes\Db2\DB\<any number of subdierctories>\file.bin

Server BLAH\D:\Indexes\Db3\DB\<any number of subdierctories>\file.bin

The file could exist in either or all of the DB1, 2, 3<any subdirectories. I want to try to find out if it exists, if so, which directory and then mail that info to the user (it's basically trying to monitor an error flag and notify).

Is this possible ith TSQL or should I look at a VB route in DTS?

Thanks,

M.

 

[mutley1]

Hi Denny,

Well, I spent the day reading up and trying to write a cursor, but I am completely lost. I have the list of full paths (obviously), but want to get that as well as the xp_getfiledetails in one table. Would you be able to give me any further pointers please? Think I'm in too deep for my skills!

Any help would be greatly appreciated.

M.

 

[mrdenny]

Ah, read on grasshopper...
Changes are in red.

declare    @Path varchar(128) ,
@FileName varchar(128)
select    @Path = '\\SERVER\D$\Indexes' ,
@FileName = ' ISYS.*' --'file.bin'

declare @cmd varchar(8000)
create table #a(s varchar(8000))
[red]create table #details 
(aname sysname,
size bigint,
CreationDate varchar(8),
CreationTime varchar(6),
UpdateDate varchar(8),
UpdateTime varchar(6),
AccessDate varchar(8),
AccessTime varchar(6),
Attributes int)

create table #final
([filename] varchar(8000),
size bigint,
CreateDate datetime)
[/red]
select @cmd = 'dir /B /S ' + @Path + @FileName
print @cmd
insert #a exec master..xp_cmdshell @cmd
delete from #a where s is null or s not like '%bin'

[red]
declare @FileName as varchar(8000)
declare @CreateTime as varchar(6)
declare cur CURSOR for select s from #a
open cur
fetch next from cur into @FileName
while @@FETCH_STATUS = 0
BEGIN
    truncate table #details

    insert into #details
    exec xp_getfiledetails @FileName

    select @CreateTime = CreationTime
    from #details

    while len(@CreateTime) <> 6
    BEGIN
        set @CreateTime = '0' + @CreateTime
    END

    insert into #final
    select @FileName, Size, convert(datetime, substring(CreationDate, 5, 2) + '/' + right(CreationDate, 2) + '/' + left(CreationDate, 4) + ' ' + left(@CreateTime, 2) + ':' + substring(@CreateTime, 3,2) + ':' + right(@CreateTime, 2))
    from #details
END
close cur
deallocate cur
[/red]
drop table #a
[red]
select *
from #details

drop #details
[/red]

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mutley1]

Thanks a million Wise Master!

Much appreciated - will give it a test later today.

Thanks again,

M.

 

[mutley1]

I call again on my illustrious Sensee (bowing vigourously!).

Thanks for the code Denny - apologies it took so long to test but I was shifted to another project and never had a chance to test it.

It seems to be hanging for some reason - I made minor amendments because the variable @FileName is declared twice (so changed the secong half of the script to @FileName2) and added master.. to exec xp_getfiledetails @FileName (running it in a DBA database). Basically, I copied a directory on my laptop (SQL Personal Edition) that had various subdirectories and each subdir had an exe file in it. I set the path as C:\mytestfile\ (the folder to scan) and the FileName to '*.exe'. The first half works fine - picks up all the .exe files. The second part just seems to hang (200MB folder altogether and left it running over lunch and still not finished.

I know cursors are rubbish, but that seems to be a little too long for doing xp_getfiledetails for just 14 files in the #a table? I can't see anything wrong with the code - any ideas?

Thanks for all your help so far.

declare    @Path varchar(128) ,
@FileName varchar(128)
select    @Path = 'C:\mytestfile\' ,
@FileName = '*.exe' --'file.bin'

declare @cmd varchar(8000)
create table #a(s varchar(8000))
create table #details 
(aname sysname,
size bigint,
CreationDate varchar(8),
CreationTime varchar(6),
UpdateDate varchar(8),
UpdateTime varchar(6),
AccessDate varchar(8),
AccessTime varchar(6),
Attributes int)

create table #final
([filename] varchar(8000),
size bigint,
CreateDate datetime)

select @cmd = 'dir /B /S ' + @Path + @FileName
print @cmd
insert #a exec master..xp_cmdshell @cmd
delete from #a where s is null or s not like '%exe'

--select * from #a


declare @FileName2 as varchar(8000)
declare @CreateTime as varchar(6)
declare cur CURSOR for select s from #a
open cur
fetch next from cur into @FileName2
while @@FETCH_STATUS = 0
BEGIN
    truncate table #details

    insert into #details
    exec master..xp_getfiledetails @FileName2

    select @CreateTime = CreationTime
    from #details

    while len(@CreateTime) <> 6
    BEGIN
        set @CreateTime = '0' + @CreateTime
    END

    insert into #final
    select @FileName2, Size, convert(datetime, substring(CreationDate, 5, 2) + '/' + right(CreationDate, 2) + '/' + left(CreationDate, 4) + ' ' + left(@CreateTime, 2) + ':' + substring(@CreateTime, 3,2) + ':' + right(@CreateTime, 2))
    from #details
END
close cur
deallocate cur

drop table #a

select *
from #details

drop table #details

drop table #final