August 22, 2017 at 7:53 am
Hi all
At my workplace, we use a SQL Server based application - Dynamics AX. Using this application, we create all our sales invoices. At the time of invoice creation, the system emails all our invoices to the customer when they are generated. This process also stores an archive copy on our file storage.
Our requirement has now changed to only store those invoices which are still outstanding and have not been paid. I have already come up with the TSQL that returns a list of all outstanding invoices - this list would look like the below:
InvoiceID
254425
223352
225854
225685
295568
etc
The invoice archive copies are stored with a filename that corresponds to the InvoiceID, eg for invoice 254425, the filename would be 254425.pdf.
How can I use the list of unpaid invoices returned from the SQL query, to delete all files from the archive location whose filename does not match one of the items in the SQL query results? Ideally, I would want this task to run by itself every week without user intervention, unless there was an error in which case I get an email to say the task couldnt run
This problem has been bugging me for a while, so any help would be most appreciated
Cheers
August 22, 2017 at 9:00 am
As much as I hate to suggest it, this sounds like a job for a cursor.
Do you have xp_cmdshell enabled or access to use it? You will need that enabled in order to delete files from the file system (unless you are using filestream).
If so, can you invert that list? What I mean is can you generate a list of ones that are safe to delete? If so, I think you'd want something like this:DECLARE @filename VARCHAR255)
DECLARE @query VARCHAR(1024)
DECLARE cursewords CURSOR LOCAL FAST_FORWARD
FOR
<your select statement>
OPEN [cursewords]
FETCH NEXT FROM [cursewords] INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @query = 'del \\path\to\file\' + @filename + '.pdf'
EXEC xp_cmdshell @query
FETCH NEXT FROM [cursewords] INTO @filename
END
NOTE - I didn't try running this. What I would do first is replace the SELECT @query line with:SELECT @query = 'ECHO del \\path\to\file\' + @filename + '.pdf >> C:\testing.txt'
presuming you are an administrator... if you are not, then change the path to the testing.txt file to somewhere you have write access to. Then open testing.txt to see if the file generated correctly. It should list all of the commands it is going to run. Repeated runs of the above will make the testing.txt file grow as it doesn't truncate the file first. So if you are running it more than once, make sure to manually delete or empty testing.txt first.
Once testing.txt looks like it will be removing the correct files, I would back up the folder (JUST in case it screws up) and then run the original query and it should remove all of the files you are wanting removed.
EDIT - forgot to add the .pdf onto the query.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 22, 2017 at 12:19 pm
I'd go straight to PowerShell for this. You can run the query, output it to a result set. Use the result set to delete the files. Way too simple.
Invoke-Sqlcmd to get the query to run. Use the Remove-Item command with a path to remove the files.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2017 at 2:58 pm
DECLARE @i table --Unpaid
(
InvoiceID int
);
INSERT @i VALUES (254425), (223352), (225854), (225685), (295568);
DECLARE @t table
(
file_name nvarchar(100)
, depth tinyint
, is_file bit
);
INSERT @t EXEC sys.xp_dirtree 'C:\PathToTheFiles', 0, 1;
DECLARE c CURSOR FOR
SELECT
'DEL ' + t.file_name
FROM
@t t
LEFT JOIN
@i i
ON Cast(Left(t.file_name, 5) AS int) = i.InvoiceID
WHERE
i.InvoiceID IS NULL;
DECLARE @cmd nvarchar(500);
OPEN c;
FETCH c
INTO
@cmd;
WHILE @@Fetch_Status = 0
BEGIN
EXEC sys.xp_cmdshell @cmd, no_output;
FETCH c
INTO
@cmd;
END;
August 22, 2017 at 3:42 pm
here's a quick and dirty version of powershell, detailing what Grant was referencing
$FileNames = Invoke-SQLCMD -Query "SELECT top 5 'C:\Data\' + name + '.sql' from master.sys.tables" -ServerInstance "TSFC095"
$FileNames #echo out the collection for debugging
foreach($file in $FileNames)
{
$file #echo out the value for debugging
if(Test-Path -Path $file)
{
Remove-Item $file
}
else
{
Write-Output "File Not Found to Delete $file"
}
}
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply