Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get date last modified for file Expand / Collapse
Author
Message
Posted Friday, May 02, 2008 10:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 8:47 AM
Points: 134, Visits: 565
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

Post #494395
Posted Friday, May 02, 2008 10:59 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:32 AM
Points: 4,056, Visits: 5,182
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
Post #494431
Posted Tuesday, May 06, 2008 6:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 8:47 AM
Points: 134, Visits: 565
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?
Post #495541
Posted Tuesday, May 06, 2008 9:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 4:38 AM
Points: 416, Visits: 541
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
Post #495720
Posted Tuesday, May 06, 2008 10:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Post #495765
Posted Tuesday, November 04, 2008 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 05, 2008 4:50 AM
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






Post #596400
Posted Wednesday, August 12, 2009 2:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, 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
Post #769654
Posted Thursday, September 24, 2009 3:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 7:51 PM
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
Post #793593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse