April 4, 2008 at 11:53 pm
Comments posted to this topic are about the item Delete files older than n-days via T-SQL
Your friendly High-Tech Janitor... 🙂
June 5, 2008 at 11:08 am
The 'Forfiles' command can do this in one line of code. I don't know why we have to re-invent the wheel here.
June 5, 2008 at 11:18 am
Forfiles is not included in every os by default. I needed to make sure that the code was supportable with no additional external software requirements (resource kit executables and such).
The Forfiles command would make life quite a bit simpler and it could be easily added to it and the FOR command removed...
Your friendly High-Tech Janitor... 🙂
June 6, 2008 at 12:48 am
Thanks for the script.
Months ago I was looking for a scritp like this one with an additional feature: recursively delete old files in the selected folder an sub-folders.
I suppose it is difficault to deal with sub-folders at any level using T-SQL and finally I use an external exe program written in C# (deleteold.exe from codeproject).
Anyway, I would prefere this functionality in T-SQL 😉
Regards
June 11, 2008 at 9:09 am
Just curiuos if anyone is using this? I'm planning to implement this at a disaster recovery site. On our live SQL Server we back up the db and ftp it to another location. I figure that I can run this as a job at the DR site to delete the old backups.
June 11, 2008 at 10:04 am
The code presented is actually about six months older than the date on it - I've been using this method on around 150 servers since it's inception with no issues.
Edit:
FWIW, you can add a PRINT @DynDelete and if you schedule the code through SQLAgent you'll have the delete command output so you can record what is being deleted by having the job step append its output to a file. [My production version of this logs the deletes to a table]
Your friendly High-Tech Janitor... 🙂
June 11, 2008 at 10:13 am
thanks
July 29, 2008 at 3:56 am
If you want to use SQL instead of other applications and you're using SQL Server 2005, why you don't use the extended procedure xp_delete_file?
Personally I feel it quite restricted and I still use a delete command line that I create for myself when I was using SQL Server 2000.
August 5, 2008 at 12:51 pm
I'm trying it and getting all kinds of incorrect syntax error when parsing it.
August 5, 2008 at 12:56 pm
It might be the extra characters that are 'added' to the script when posted.
Your friendly High-Tech Janitor... 🙂
August 5, 2008 at 1:03 pm
What 'added' characters?
August 5, 2008 at 1:22 pm
The scriptbox appears to add characters (or uses a diff charset) for tabs.
What errors are you getting?
Edit:
If you are indeed only parsing it and not compiling it, it will generate errors as the temptables are not created if you only parse it (a very annoyying feature).
Your friendly High-Tech Janitor... 🙂
August 5, 2008 at 1:39 pm
I get the same errors either way.
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 41
Line 41: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 59
Line 59: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 65
Line 65: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 73
Line 73: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 77
Line 77: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 80
Line 80: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 89
Line 89: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 101
Line 101: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 117
Line 117: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 126
Line 126: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 127
Line 127: Incorrect syntax near '?'.
Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 133
Line 133: Incorrect syntax near '?'.
August 5, 2008 at 1:45 pm
As I suspected...the tabs are being converted in the scriptbox when submitted.
I'm not sure what to tell you except to do a search & replace on the tab character. It fails for me with the same errors and it's defenitly the tabs being incorrectly converted when scripts are submitted.
Your friendly High-Tech Janitor... 🙂
February 9, 2011 at 2:51 pm
ForFiles does'nt support UNC paths, that is why i am trying to use this code 🙂
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply