Delete files from disk, with a job, respecting some rules ( SQL Server 2008 ) We have An application that our customers use to download and upload files.

I need to delete these files from disk after 30 days, of if it was downloded, in 7 days.

How can i Use the script below, in a powershell, to do this task?

select ArquivoServidor from MyTable
  where DATEDIFF(day,LogDownload,GETDATE())&gt;= 7
    or DATEDIFF(day,LogUpload,GETDATE())&gt;= 30


( i'm not in the server where files are )

Thank you very much.

Edit1:

I can use this ( After a lot of tests, it's working ):

    create table #temp 
    ( ArquivoServidor varchar (max))
go
insert into #temp 
select 'xp_cmdshell '+ ''''+'del '+ArquivoServidor+''''+';' from MyTable
  --set Arquivo = NULL
  where DATEDIFF(day,LogDownload,GETDATE())&gt;= 7
    or DATEDIFF(day,LogUpload,GETDATE())&gt;= 30

    select * from #temp where ArquivoServidor is not null
        drop table #temp


it will show me something like this:


  xp_cmdshell 'del \remote_server\folder\folder\file.txt'


I'm thinking in something like execute sql, But Im having problems with files with spaces in name ( jhon vacation.mp3 ) for example.

Any Ideas?

Edit2:

Query done, now I need to fix the Spaces in files name.

declare @sql varchar(max)
set @sql =

'select ''exec xp_cmdshell ''
    + ''''
    +''del ''
    +ArquivoServidor
    +''''
    +'';''
    + CHAR(13)
    + CHAR(10) 
from Mytable
  --set Arquivo = NULL
where 
    ArquivoServidor is not null 
        and DATEDIFF(day,LogDownload,GETDATE())&gt;= 7
    or DATEDIFF(day,LogUpload,GETDATE())&gt;= 30 
        and  ArquivoServidor is not null'''






$Computer = Read-Host "Input Computer Name (remote PC)"
$Path = Read-Host "Path of temp folder"
If((Test-Path $Path) -eq $True) {
    Invoke-Command -ComputerName $Computer -ScriptBlock { 
        $File_Counts = (Get-ChildItem -Path $Path | Measure-Object).Count
        $FPath = $Path + "\*.*"
        Remove-Item -Path $FPath }
    $OutPut_File = "RemoteDeletes_log.txt"
    $File_Counts.ToString() + " files have been deleted from this $Path folder on remote $Computer PC" &gt;&gt; $OutPut_File
    Invoke-Item $OutPut_File
} else {
    Write-Host "Folder isn't exist, please input correct path"}


It doesn't follow the rules that I need.
Try something like this:
-Install SQL 2012 feature pack to get invoke-sqlcmd or change it to use sqlcmd.

-You can just pass in the raw TSQL but I would turn it into a stored procedure and just pass that in. Something like:

CREATE PROCEDURE [GetDeleteDate]
AS
select ArquivoServidor from tblExchange
  where DATEDIFF(day,LogDownload,GETDATE())&gt;= 7
    or DATEDIFF(day,LogUpload,GETDATE())&gt;= 30




####Loop through results and execute######
##Define the return as an array by putting it in @()
$lol = @(Invoke-Sqlcmd -Server $ServerInstance -Query "exec [YourAdminDatabase].[dbo].[GetDeleteDate]" | select-object -Expand Column1)  


This will now create an array with the return item as 1 value.  You can now use this array in a loop in powershell to perform whatever work you need done and add error handling.  If you need to then execute a new SQL query and pass in the variable you'd do something like this:

#$lol.Count  ##this is to test how many items are in your array.
foreach ($row in $lol) 
{
#Write-Output $row
Invoke-Sqlcmd -Server $ServerInstance -Query $row -verbose
}


Or if you want to pass the variable as a item to delete in a loop in powershell, feel free to do that too.  The $lol variable is an array with all the data requested.  At that point you can do whatever you need to do with it in PS using the loop above.  It could be a simple del, remove-childitem, etc. Whatever you're comfortable with.  Let us know if you need help writing the delete portion but in the comments you mentioned you're familiar with PS so I figured you could use what you like best.  If you have spaces in your file path make sure to encapsulate it within single or double quotes.