Check File Creation Date in SQL Server 2005

Posted on 2011-10-05
Last Modified: 2012-05-12
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?
Question by:fishbowlstudios
    LVL 21

    Expert Comment

    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.



    Author Comment

    I'm sorry. Is there a way to do it in SQL Server? Examples would be helpful. :)
    LVL 5

    Expert Comment

    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
    LVL 13

    Accepted Solution


    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,


    Author Closing Comment

    I couldn't really use any of provided solutions. I ended up writing a powershell script to handle the compare. Thanks!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now