Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get date last modified for file


Get date last modified for file

Author
Message
Sam -480699
Sam -480699
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 642
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
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5823
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
Sam -480699
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 642
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
vidhya sagar
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 620
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
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36083 Visits: 18738
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).

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Paul Grib
Paul Grib
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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
kwilt
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 394
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
thedaver79
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search