Delete multiple files from folder using T-SQL without using cursor

I am writing a cleanup script. This script will run on weekend and clean up the db. Tables are related to Eamils and path of attachments are being stored in table. In cleanup of tables I also have to delete files from folder.

The path of files is like following.

\\xxx.xxx.xxx.xxx\EmailAttachments\Some Confirmation for xyz Children Centre_9FW4ZE1C57324B70EC79WZ15FT9FA19E.pdf

I can delete multiple files like following.

xp_cmdshell 'del c:\xyz.txt, abc.txt'

BUT when I create a CSV from table using FOR XML PATH('') the string cut off at the end. There might be 1000s of rows to delete so I don't want to use cursor to delete files from folder.

1. How can I delete files from folder without using cursor
2. What permissions do I need on network folder to delete files using t-sql from sql server

EDIT: I have used cursor and it looks ok, not taking so much time. One problem which I am facing is

• The sql server consider file name with space as two files like follow开发者_如何学Pythoning statement

  xp_cmdshell 'del E:\Standard Invite.doc'

throws error

Could Not Find E:\Standard
Could Not Find C:\Windows\system32\Invite.doc
NULL

Thanks.

Personally, I wouldn't worry too much about using a cursor here. Cursors are only 'mostly evil'; as your task isn't a set-based operation a cursor may be the most effective solution.

Although you have a comment stating that it will take an "awful lot of time" to use a cursor, in this case the biggest overhead is the actual delete of the file (not the cursor).

Note: The file deletion is done by the Operation System, not by the RDBMS.

As the delete is being done by calling xp_cmdshell, and because it it a procedure (not a function, etc), you can't call it and pass in a table's contents.

What you could do is build up a string, and execute that. But note, you are limitted to a maximum of 8000 characters in this string. As you have already said that you may have thousands of files, you will certaily not fit it within 8000 characters.

This means that you are going to need a loop no matter what.

DECLARE
  @command   VARCHAR(8000),
  @next_id   INT,
  @next_file VARCHAR(8000),
  @total_len INT

SELECT
  @command   = 'DEL ',
  @total_len = 4

SELECT TOP 1
  @next_id   = id,
  @next_file = file_name + ', '
FROM
  table_of_files_to_delete
ORDER BY
  id DESC

WHILE (@next_file IS NOT NULL)
BEGIN
  WHILE ((@total_len + LEN(@next_file)) <= 8000) AND (@next_file IS NOT NULL)
  BEGIN
    SELECT
      @command   = @command + @next_file,
      @total_len = @total_len + LEN(@next_file)

    SELECT
      @next_file = NULL

    SELECT TOP 1
      @next_id   = id,
      @next_file = file_name + ', '
    FROM
      table_of_files_to_delete
    WHERE
      id < @next_id
    ORDER BY
      id DESC
  END

  SET @command = SUBSTRING(@command, 1, @total_len - 2) -- remove the last ', '

  EXEC xp_cmdshell @command

  SELECT
    @command   = 'DEL ',
    @total_len = 4
END

Not pretty, huh?

What you may be able do, depending on what needs deleting, is to use wild-cards. For example:

EXEC xp_cmdshell 'DELETE C:\abc\def\*.txt'

To delete files with space in name you need to enclose the filename with "

xp_cmdshell 'del "E:\Standard Invite.doc"'

DECLARE @deleteSql varchar(500)
   ,@myPath  varchar(500) = '\\DestinationFolder\'

SET @deleteSql = 'EXEC master..xp_cmdshell ''del '+@myPath +'*.csv'''
EXEC(@deleteSql)