remote deleting files, where names are in a table, across servers
I have a table that lists file-names that have been created on an App server. Goal: I want to keep the list of files in the SQL server table and the list of files on disk on the App server in sync. The table has the file name in it (nothing else). The App server name can be hard coded. The directory where the files are located can be hard coded. The list of file-names changes each day and needs to be purged nightly off of the SQL server table and the App server directory. I can write a set of SQL to select the files to delete no problem. I have not been able to find a way to remotely delete on the app server. I have explored xp_cmdshell (if I were deleting on the SQL server I would be done), mstsc, rdp looking for remote execution of a DEL command. I have not been able to figure a way to invoke deleting those files from the SQL server. Any help is appreciated. Thank you for your help and time.
Have you considered running something on the app server that reads the table and deletes the files? It sounds like a simple C# app could query the table, loop through the rows, and delete the files/rows.