March 26, 2009 at 12:58 pm
I have folder c:\test. In this test folder, I have a bunch of files.
I wanted to delete these files if they are more than 5 days old.
how can i do this in SQL?
any help would be appreciated.
March 26, 2009 at 1:13 pm
The real question is why do you need to do this in SQL?
Much easier to create a batch file or powershell script to delete files on a regular basis - then schedule that to run using Task Scheduler.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2009 at 1:27 pm
Thanks for responding. I wanted to do it in sql to housekeep some admin task in sql.
besides, i don't know dos and other command programming to maintaint it.
March 26, 2009 at 1:36 pm
There is no native way to delete OS files from SQL Server. The basic options are:
xp_cmdshell - allows you to call out to the OS and issue OS commands. Disabled by default and you should not enable it due to security risks.
SQL Server Agent Job - has option to run commands at the OS level. But, you would still need to create a batch script or powershell script or vbscript or...
SQL CLR - would need to create a .NET assembly to delete the files, then you could call it from a SQL procedure. This is not really an option because of the way you would have to implement the CLR procedure, and besides - you don't really know other languages.
So, my recommendation would be to research Powershell. This is really easy to do from there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2009 at 1:50 pm
Thanks for the info. I see what you mean.
I was looking into File System object in SQL to do this,
or using SSIS. But I'll look into PowerShell as well and
see if the company ok for it should it be implemented in
production.
March 27, 2009 at 12:43 pm
sqlblue (3/26/2009)
... PowerShell...
Powershell? Wish you the best :-), it can do almost everything, but the syntax is just ugly in my opinion...
Greets
Flo
March 27, 2009 at 1:23 pm
Florian Reischl (3/27/2009)
sqlblue (3/26/2009)
... PowerShell...Powershell? Wish you the best :-), it can do almost everything, but the syntax is just ugly in my opinion...
Greets
Flo
Ah, Flo - it's not that bad...
PS> Get-Childitem -Recurse | ? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | % {Remove-Item $_.Name -Whatif}
I added the -Whatif just to make sure before we actually delete anything...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 27, 2009 at 1:47 pm
Jeffrey Williams (3/27/2009)
PS> Get-Childitem -Recurse | ? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | % {Remove-Item $_.Name -Whatif}
:w00t:
So that's exactly what I'm speaking about...
Fortunately I don't have to. I'm a developer, not an administrator (our DBAs use perl and currently runnin' into problems with x64...).
Since .Net has an on board compiler, I wrote my own C# based scripting engine. The scripts are a little bit larger but it works very fast and - much more important - I can read it 😉
Greets
Flo
March 27, 2009 at 2:00 pm
Florian Reischl (3/27/2009)
Jeffrey Williams (3/27/2009)
PS> Get-Childitem -Recurse | ? {$_.LastWriteTime -lt (get-date).AddDays(-3)} | % {Remove-Item $_.Name -Whatif}:w00t:
So that's exactly what I'm speaking about...
Fortunately I don't have to. I'm a developer, not an administrator (our DBAs use perl and currently runnin' into problems with x64...).
Since .Net has an on board compiler, I wrote my own C# based scripting engine. The scripts are a little bit larger but it works very fast and - much more important - I can read it 😉
Greets
Flo
Oh - you think perl is easier to read? 😛 And, what problem do you have with the above, it's simple to read.
Get all child items recursively (loop through each directory/sub-directory), where (? operator) the last write time is less than (or should that be greater than, can never remember that ;)) a date and remove the item. 😀
Well, maybe not that simple to read... 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 27, 2009 at 2:13 pm
I would never say that perl is easier to read :-P. Just my admins do.
As a said, I am using C# scripts:
using System;
using System.IO;
void Main()
{
string[] files = Directory.GetFiles(@"C:\Users\Flo\Temp");
foreach (string name in files)
{
if (File.GetLastWriteTime(name) < DateTime.Now.AddDays(-7))
File.Delete(name);
}
}
Sure, much more code but that's okay for me since hard disks become bigger and bigger and much more readable (for a developer).
Greets
Flo
March 27, 2009 at 4:28 pm
Thank you both Flo and Jeffrey.
I did download the PowerShell last night, and have a brief look around, and even
checked out one of its introduction tutorials, and I have to agree with Flo --
the code is hard to read and understand (I'm sorry Jeffrey :(, I guess it really
depends on each person.).
Anyway, I finally figured out to do it in SQL using xp_cmdshell. It is already enabed
by the DBA, so I was be able to use it in my code. But I did it the long way though
-- gettting the files name into a temp table, and then delete one by one
from matching the filename from the table to the file in the directory. The way
Flo has it in C# looks much shorter and nicer.
March 28, 2009 at 5:10 am
If you want, I can attach the C# source code and/or the assembly (if you trust me :-D).
I just don't know if the forum allows to attach a zipped executable this.
Greets
Flo
March 28, 2009 at 6:50 am
you're probably right, i doubt that the forum allow attachement.
but can i just copy the code you have here and put it in a
class, compile it and use it?
March 28, 2009 at 9:41 am
sqlblue (3/28/2009)
you're probably right, i doubt that the forum allow attachement.but can i just copy the code you have here and put it in a
class, compile it and use it?
The forum does allow attachments and you could put it all in a zip and attach it.
This is one of the reasons I stick with scripting (and, yeah - it can be cryptic at times). With scripting there is no need to compile anything and making changes is very easy.
But, that would just be me 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2009 at 10:38 am
Hi sqlblue
Because Jeffrey is absolutely correct I will not attach any compiled assembly ;-).
You asked if you just copy-paste the class so I guess you have Visual Studio. I just created a solution with all the (two...) files you need. I also added a small ReadMe and a example "test.csscript". The RaedMe.txt should explain the most things.
Just download, analyze the code, compile and use it.
Greets
Flo
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply