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.

 

[vbkris]

have you tried the xp_cmdshell sp? using this you can do directory options...

Known is handfull, Unknown is worldfull

 

[SQLDenis]

Returns 1 if a file exists, 1 if the file is a directory and 1 if a parent directory exists
master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mutley1]

Thanks guys,

Thisng is it might be in

Server BLAH\D:\Indexes\Db1\DB\10\file.bin
AND / OR
Server BLAH\D:\Indexes\Db1\DB\11\file.bin
AND / OR
Server BLAH\D:\Indexes\Db1\DB\12\file.bin
AND / OR
Server BLAH\D:\Indexes\Db2\DB\100909\file.bin
AND / OR
Server BLAH\D:\Indexes\Db2\DB\11hf878\file.bin
AND / OR
Server BLAH\D:\Indexes\Db2\DB\12ygu6\file.bin
AND / OR
etc. etc.
I really need to get the pathname back of the directory it is in.

Thanks,

M.

 

[Jamfool]

you could use:

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

declare @cmd varchar(8000)
create table #a(s varchar(8000))

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

select * from #a

drop table #a

 

[Jamfool]

just to clarify the /s switch is recursive. so your search would be from D:\Indexes\ level

 

[mutley1]

Thanks All,

Will try later today.

Much appreciated.

M.

 

[mutley1]

Hi Jamfool,

Thanks - I tried that with the below but it comes bacl as file not found, and I know there are several versions of the file i was looking for (ISYS.net) I think it is only returning the results of the last directory searched (from what i have done with a normal "dir" command from cmd prompt.

declare    @Path varchar(128) ,
@FileName varchar(128)
select    @Path = '\\SERVER\d$\Indexes' ,
@FileName = 'ISYS.net' 

declare @cmd varchar(8000)
create table #a(s varchar(8000))

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

select * from #a

drop table #a

thanks,

M.

 

[Jamfool]

try mapping a drive letter on the server to

\\SERVER\d$\Indexes

 

[mutley1]

Sorry Jamfool - that doesn't work either. Network drive mapped as I:\Indexes and that is showing as "system cannot find path specified" (have amended script to represent drive letter).....

 

[gmmastros]

You should be able to tell what shares are available by running this.

exec master..xp_cmdshell 'net use'

You are probably running in to a security issue. When using xp_cmdshell, it runs under the account that sql server is running on.

On the SQL Server computer...
Click start -> run
type services.msc
scroll to MSSQLSERVER
right click -> properties
Logon tab.

This will tell you what account SQL Server is running under. You'll need to make sure that the share on the other computer is allowing at least read-only permissions for this account.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jamfool]

run the command from dos.

dir /s /b z:\Indexes\*.*

 

[mutley1]

There was a bit of white space in the code so I've removed it and am getting results back now. Thanks all.

 

[mrdenny]

You can also get this info via T/SQL.

exec master.dbo.xp_dirtree '\\server\share\', 0, 1

This will output a three column list. The first column is File or Folder name. The second column is dept to the file, and the third column is if it's a file or folder.

You can take the output of this procedure, dump it into a temporary table with an identity column on it, then fetch through it and make a more usable looking tree out of it.

And all with out the security issues of using xp_cmdshell.

The first paramater is the path to search. The second is how many levels to go down. 0 pulls everything. The third is include files or exclude files. 0 to exclude, 1 to include.

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 Denny,

I like the look of that, but construction with identities etc. is a bit way over my head. The other thing I need to try and do is retrieve the date of the file (not a getdate timestamp, the created date of the file). The reason is the file I am looking for can be there legitimately, but if it is there for more than 15 minutes then it is a problem. However, in the 15 minutes between this running, it might have been removed and replaced which means it is not an issue. Basically, I need to run it and compare if the times of the file creation have changed. I don't think tha't possible with xp_dirtree - is there anything Ican add to xp_cmdshell guys (using Jamfool's prior code)? I have a feeling the answer is no because of the /B flag......:-(

Thanks,

M.

 

[mrdenny]

You can use xp_getfiledetails to get all sorts of good info about files and folders in the file system.

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 Denny,

I've just had a look at the output and it seems ideal. Once I have the table created with the relevant info from the @cmd in this code, how would I go about updating it with the creationdate and creationtime from xp_getfiledetails? Basically, once I have a table of possible candidates, I need to run through each one to append the date an time. Sorry - would you be able to help?

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))

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'

select * from #a

drop table #a

gives

FULLPATH\FILE1
FULLPATH\FILE2
etc.

I would then like to run through these to get the final info of

FULLPATH\FILE1 Creationdate Creationtime
FULLPATH\FILE2 Creationdate Creationtime

Any help much appreciated as I am a bit out of my league here.

Thanks,

M.

 

[mrdenny]

You'll need to use a cursor and fetch through the records in the #a table and run xp_getfiledetails for each one.

Create a temp table and do

insert into #filedetails
exec xp_getfiledetails @variable

You can then get the date and time from the #filedetails table.

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]

Time to start reading up on code!

Thanks Denny.

 

[mrdenny]

no problem.

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