SQL Stored Procedure to delete old Microsoft Dynamics GP Document Attachments

Microsoft Dynamics GPThe Document Attachment feature was introduced in Microsoft Dynamics GP 2013 RTM and has been enhanced a number of times since. One of the features it does not have is the ability to delete attachments; you can flag them as deleted, but they are not removed from the database.

With GDPR rules, clients have become concerned about the information retained in the system without a means to delete it. To that end I created a SQL stored procedure which could be scheduled to run on a regular basis and delete transactions older than the specified number of years (highlighted value is the number of years).

This allows clients to run this on a scheduled basis and remove old documents; it can also serve as the basis for a customised version which deletes on a more controlled basis.

As with any script, please ensure you perform through testing before deploying to a live system.

IF OBJECT_ID (N'usp_AZRCRV_DeleteDocAttachAttachments', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_DeleteDocAttachAttachments
GO

CREATE PROCEDURE dbo.usp_AZRCRV_DeleteDocAttachAttachments
	@iAge INTEGER = 7
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
-- CREATE TEMPORARY TABLE CREATE TABLE #AttachmentsToDelete( Attachment_ID CHAR(37) ) -- SELECT ATTACHMENTS OVER n YEARS OLD TO DELETE INSERT INTO #AttachmentsToDelete (Attachment_ID) --VALUES ( SELECT Attachment_ID FROM CO00101 WHERE CREATDDT < DATEADD(yyyy, -@iAge, GETDATE()) ) -- DELETE FROM Document Attachment Master (CO00101) DELETE FROM CO00101 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Reference (CO00102) DELETE FROM CO00102 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Properties (CO00103) DELETE FROM CO00103 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Status (CO00104) DELETE FROM CO00104 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Email (CO00105) DELETE FROM CO00105 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM COATTACHMENTITEMS DELETE FROM coAttachmentItems WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DROP TEMPORARY TABLE DROP TABLE #AttachmentsToDelete GO

What should we write about next?





Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *