Day: January 28, 2020

The 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 an IT Professional (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