May 2, 2008 at 10:14 am
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
May 2, 2008 at 10:59 am
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
May 6, 2008 at 6:19 am
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?
May 6, 2008 at 9:07 am
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
May 6, 2008 at 10:13 am
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).
November 4, 2008 at 2:56 am
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
August 12, 2009 at 2:13 pm
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
September 24, 2009 at 3:26 pm
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy