SQL Clone
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 Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 643
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11137 Visits: 5985
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 Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 643
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1828 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 Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142092 Visits: 19421
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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