Friday, August 3, 2012

Deleting Files with a SQL Job in SQL Server-2008

Suppose you want to delete some files from a directory. This could be one of the easiest task for anyone who is familiar with windows. But what if we want to delete the files in a regular interval, to do this we have some options like:
·         By a windows service that keeps on deleting files in a regular interval.
·         By a SQL Job (Taking advantage of SQL Server Agent Service) that deletes files in a certain interval.

Here I am going to discuss “Deleting files through SQL Job”. In SQL job, you need to put some SQL query in your SQL Job Steps.

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.

To know “How to create SQL job”, you can go to my article in site at:

----------------------------------- Enabling the COMMAND SHELL ------------------------------

--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.

----------------------------------- Deleting the files in ------------------------------------
-- Suppose we need to delete all .txt files under "D:\temp\Testing\"

xp_cmdshell 'DEL D:\temp\Testing\*.txt'


No comments:

Post a Comment