Get file names, last updated from a directory

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get file names, last updated from a directory

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-12 : 08:49:00

Jenny writes "Hi,
I need to access a folder and get the information about the file names, last updated within that folder and then decide whether to email the file or not. How can I approach this in my stored procedure?
Thanks."

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-12 : 09:12:34

You can use xp_cmdshell to execute a "dir" command, and store the result in a temporary table. Then you can maipulate the data and get the information you want.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-12 : 09:45:26

Here's something I wrote earlier to get copy the latest file from a server to my m/c (because I don't have access to it but sql server does)

the /B gives the names only in the temp table - omitting it means you will have to find the name but you will get the date as well.
/O-D sorts latest first.

declare @from varchar(100), @to varchar(100), @sql varchar(1000)

select @from = '\\server\directory\', @to = '\\myPC\share\directory\'

create table #a (s varchar(2000), id int identity)
select @sql = 'dir /B /O-D ' + @from + '*.*'
insert #a exec master..xp_cmdshell @sql
select @sql = 'copy ' + @from + #a.s + ' ' + @to
from #a where id = 1
select @sql
exec master..xp_cmdshell @sql
drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-12 : 10:03:57

Have you played with exec master.dbo.xp_getfiledetails ?

Jay White
{0}

jennyf
Starting Member

3 Posts

Posted - 2002-11-13 : 11:05:46

Thanks for your replies. I will try both xp_cmdshell and xp_getfiledetails. BTW, I can't find xp_getfiledetails in my sql books online. Is this undocumented? I use sql server 7. Again, thanks all for your help.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-11-13 : 13:10:39

yes, another undocumented one. It's somewhat easy and returns useful info.

master.dbo.xp_getfiledetails 'filepath'

example

master.dbo.xp_getfiledetails '\\corpsql\localfile\test.txt'

this returns

Alternate Name ,Size,Creation Date,Creation Time,Last Written Date ,Last Written Time, Last Accessed Date, Last Accessed Time, Attributes

edit note.. I'm not sure what attributes it returns. For a file on my machine it says 32. You'll have to look up exactly what that means if you wanted to use it. I imagine nothing more the read only/archived/hidden and anything else like that on it.

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 11/13/2002 13:12:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources