SQL 2005 reading file attributes

Question

SQL 2005 reading file attributes

fahd malik

SSC Enthusiast

Points: 193
More actions
December 12, 2007 at 3:50 pm

#181247

In SQL 2005, is it possible to read the date modified of a file which is located on the hard drive of the server? Is there a procedure/function that would allow you to do so? I know it can be done using CLR Integration/SSIS/xp_cmdshell, but without using these?

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

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

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 1

I haven't come across any. Last time I had to do that sort of thing I wrote a VBS script to handle it (delete files older than x). And that was easy because I didn't have to return anything back to SQL Server, just run it from a SQL Agent job.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Jason Adams SSC Eights! Points: 853 More actions · Answer 2

You can probably do it with the sp_OACreate, but if you are in 2005 you are better off using a CLR function/procedure

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 3

There is no way of doing it without going outside of SQL in some way, but there are several ways of doing.

My recommendation would be use xp_cmdshell with a simple directory command then extract the date stamp from the target file.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

timothyawiseman (12/12/2007)
There is no way of doing it without going outside of SQL in some way, but there are several ways of doing.

Ummm.... not quite true in 2k or 2k5, Timothy... try this just for grins 😉

EXEC Master.dbo.xp_GetFileDetails 'C:\Config.Sys'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fahd malik SSC Enthusiast Points: 193 More actions · Answer 5

this sp Master.dbo.xp_GetFileDetails only exists in SQL 2000

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Oh, crud... you're right... I just loaded 2k5 over the weekend and verified what you say to be true... it's not in 2k5... reportedly it was and I took someone's word for it. Thank you for the correction...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 7

Well, I stand corrected in 2K at least. I have only used 2K5 for a while now, but always good to learn things I didn't know before.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/