Check File Creation Date in SQL Server 2005

I need to use a job to check the creation date of a particular file and then if it is not within 24 hours then I need to send an email to alert someone that the file hasn't changed.

I cannot use OLE scripting. Is there an easy way for this using tsql in 2005?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you are talking about a file in the file system, there isn't a real easy way to do it.  You could use xp_cmdshell to execute a DOS command like dir.  Capture that in a temp table and do some string manipulation to capture the date.  Kind of kludgy, but it has worked for me in the past.


fishbowlstudiosAuthor Commented:
I'm sorry. Is there a way to do it in SQL Server? Examples would be helpful. :)
This should get you started:

declare @FileName varchar(255)
declare @Cmd varchar(8000)
declare @FileDate datetime

create table #MyTable (TextRow varchar(8000))

set @FileName = 'c:\MyFile.xml'
set @Cmd = 'dir ' + @FileName + ' /TC'

insert #MyTable exec master..xp_cmdshell @Cmd
Hi  Following is the sample
You can do like that
Or Make slight Changes according you convenience

select @FileDate = left(TextRow,20)
from #MyTable
where TextRow like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]
[0-9][0-9]:[0-9][0-9] %'

select @FileDate
select * from #MyTable /* for further understanding */

For more Information this link helps you

It is not possible to get file information directly using T-SQL, however, you still can call COM object directly from your T-SQL without have to create any OLE scripting file - instead directly from your TSQL code.

You can have an example there on calling FSO to delete file. FSO can do more than deleting file, it can also return you the date of file created. Here is some basic of FSO,

You may be like to use, sa_OAGetProperty after calling sp_OACreate,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fishbowlstudiosAuthor Commented:
I couldn't really use any of provided solutions. I ended up writing a powershell script to handle the compare. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.