• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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?
1 Solution
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.http://www.kodyaz.com/articles/delete-file-from-sql-server-xp-cmdshell-ole-automation-procedures.aspx

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, http://msdn.microsoft.com/en-us/library/ea5ht6ax(v=vs.85).aspx

You may be like to use, sa_OAGetProperty after calling sp_OACreate, http://msdn.microsoft.com/en-us/library/ms175079.aspx

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now