Get date last modified for file

Question

Get date last modified for file

Sam -480699

SSC Eights!

Points: 975
More actions
May 2, 2008 at 10:14 am

#185048

Hi,
I am currently working on a stored procedure to bulk insert a file into a database. However I only want to perform the insert when the file has been updated (by looking at when it was last modified). I have been looking around to see if you can get the file details and store that into a temp table and the closest I have got is:
CREATE TABLE #directory_scavenge2(
[path_name] [varchar](255) NULL,
[depth] [int] NOT NULL,
[file_or_dir] [int] NOT NULL,
) ON [PRIMARY]
INSERT INTO #directory_scavenge2
execute Master..xp_dirtree 'C:\LogLoader\' , 1 , 1
However, this does not tell me when the file was last modified.
Do any of you know how this can be found?
Cheers,
Sam

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I used xp_getfiledetails for this in SQL 2000. It's not included in SQL 2005, but a couple of guys have created CLR replacements:

http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx

http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/

I haven't tried either of them yet.

Greg

Sam -480699 SSC Eights! Points: 975 More actions · Answer 2

Hi, yes I am using SQL server 2005.

Is there any other way of getting these details (without using a CLR). Or is this the only solution?

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 3

Sam (5/6/2008)
Hi, yes I am using SQL server 2005.
Is there any other way of getting these details (without using a CLR). Or is this the only solution?

Sam,

Try the below piece of code, this might help you!!!

if exists(select 1 from tempdb..sysobjects where name='##tmp')

drop table ##tmp

create table ##tmp(mdate varchar(8000))

insert ##tmp

exec master.dbo.xp_cmdshell 'dir g:\mymail.pst' -- Provide the correct filename with path

set rowcount 5

delete from ##tmp

set rowcount 0

select top(1) substring(mdate,1,20) as 'Last modified date' from ##tmp

Before executing the script make sure to change the filename with path in the query above!!!

Regards..Vidhya Sagar
SQL-Articles

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 4

sp_OACREATE and use the filesystem object to snag things.

Parse an xp_cmdshell 'dir' result set.

Or even write a VBScript that can get the infromation and insert it into your table (I'd probably do this).

Paul Grib Valued Member Points: 69 More actions · Answer 5

I'm using the following sp on SQL Server 2005:

create procedure [dbo].[get_file_info](

@file_name varchar(255)

,@file_date datetime output

,@file_size bigint output

) AS

BEGIN

declare @dir table(id int identity primary key, dl varchar(255))

declare @cmd_name varchar(255),@fdate datetime,@fsize bigint, @fn varchar(255)

set @fn=right(@file_name,charindex('\',reverse(@file_name))-1)

set @cmd_name='dir /-C '+@file_name

exec sp_configure 'show advanced options', 1;

reconfigure;

exec sp_configure 'xp_cmdshell',1;

reconfigure;

insert @dir

exec master..xp_cmdshell @cmd_name

exec sp_configure 'xp_cmdshell',0;

reconfigure;

exec sp_configure 'show advanced options', 0;

reconfigure;

select @file_date=cast(ltrim(left(dl,charindex(' ',dl))) as datetime)

,@file_size=cast(replace(substring(dl,charindex(' ',dl),255),@fn,'') as bigint)

from @dir where dl like '%'+@fn+'%'

end

kwilt SSCrazy Points: 2611 More actions · Answer 6

Thanks so much for posting this (even though it was a long time ago!) I've been searching for a solution to getting the date created for a file all day. This worked perfectly.

kay

thedaver79 Valued Member Points: 63 More actions · Answer 7

If you want to store the date of vidhya sagar query into a variable you would run this code.

DECLARE @DateInserted DATETIME

SET @DateInserted = CONVERT(DATETIME, (SELECT TOP(1) SUBSTRING(mdate,1,20) FROM ##tmp))

Thanks for all your help