Part 1: SQL Query for deleting the files
Suppose you want to delete some files from a directory. This could be one of the easiest tasks for anyone who is familiar with Windows. But what if we want to check the files periodically in some directory and if any file is found, we delete them. To do this we have some options like:
- By a windows service that keeps on checking and deleting files on a regular interval.
- By a SQL Job (Taking advantage of a SQL Server Agent Service)
Here I am going to discuss deleting files using a SQL Job. In a SQL Job, you need to use a SQL query in your SQL Job.
In the SQL query to delete the files, you need to call a predefined stored procedure "xp_cmdshell". But before calling "xp_cmdshell", we also need to enable the Command Shell of your SQL Server if it is not enabled in your system.
Here is the full SQL query that is used for deleting the files.
[Note: The topic "How to create a SQL Job in SQL Server-2008" is discussed in Part-2 of this article.]
----------------------------------- Enabling the COMMAND SHELL ------------------------------
--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
----------------------------------------------------------------------------------------------
----------------------------------- Deleting the files in ------------------------------------
-- Suppose we need to delete all .txt files under "D:\temp\Testing\"
xp_cmdshell 'DEL D:\temp\Testing\*.txt'
Part 2: Creating a SQL Job
Go to SQL Server Agent and start the Server Agent service.
Right-click the "Jobs" under "SQL Server Agent" and select "New Job…"
On clicking "New Job", the following window opens. In the left pane you see the following options:
- General
- Steps
- Schedule
- Alerts
- Notification
- Targets
In the "General" page, you need to give the name of your job as "FileDeleter" and its description.
Now go to the "Steps" page and click "New" step.
The following window appears. In this step defying window, you need to give your step name (e.g. File Delete). In the command box, paste your SQL query for the file deletion. Since in this job we have only one step, so we need to set this step as a final step. This you could do by going to the "Advanced" page under the "General" page in the same window.
On clicking "Advanced" page, you will see actions for success and failure. Set "on success action" to "Quit the job reporting success" and click OK.
Now we set the schedule of our job. Click the "Schedule" page and create a new schedule by clicking "New...". The following window appears. In this window, give the name of the job schedule and set the occurring frequency as "Daily". Then we set the timing (e.g. 12:00 AM midnight) and duration for how long this SQL job should be working (e.g. Aug 8,2012 to Nov 8, 2012). After setting these parameters, click OK to finish your SQL Job Creation.
After job creation, just right-click the newly created job "FileDeleter" and start the job.