Share via

Schedule query and delete files

Joe Green 146 Reputation points
2020-10-30T11:23:48.957+00:00

On SQL Server 11 Std version, I have two tables TableA and TableB in a database db1. TableA have columns called CreatedOn, Status and Path. Path column stores location where a file is stored on a file server outside SQL Server. TableA and TableB has one to many relationship. Also when a row is deleted from TableA, related rows from TableB are also deleted.

Now I want to run a query everyday at 11PM that will delete all the records from TableA with a Status of Pending from TableA. Also I want to delete files stored on file server related to deleted records if Path column is not empty.

Note that file server and SQL servers are two different servers.

Is this possible and if so can someone give me a clue as how to accomplish this.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,624 questions
0 comments
Accepted answer
  1. 21986829 481 Reputation points
    2020-10-30T13:13:48.39+00:00

    See this link for deleting files. It is using xp_cmdshell which needs to be on in your instance plus proper security for the account running your service.

    https://stackoverflow.com/questions/5153342/delete-multiple-files-from-folder-using-t-sql-without-using-cursor

    To delete on a different server, use '//servname/sharename/path'

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.