Share
Report
1,589I have a D365 CE environment with Data Export Services configured to sync to an Azure Synapse Database using the Append Only option.
The append only option is key because it's creating a new row every time a record is updated.
Keep in mind that the append only option is a vendor requirement for a specific integration we have so we can't change it.
To keep things clean, I need to delete all rows that are older than 48 hours. This is needed to speed up query times and also conform to cost requirements.
I tried to use the query below, but since the tables appear in the Synapse replica as "External Table" I receive an error when I try to delete them also shown below.
CREATE PROCEDURE DeleteStaleData AS BEGIN DELETE FROM [dbo].[cpp_job] WHERE [SinkModifiedOn] >= DATEADD(HOUR, -48, GETDATE()) END
Msg 46519, Level 16, State 17, Procedure DeleteStaleData, Line 11 [Batch Start Line 0
DML Operations are not supported with external tables.
My goal is to set up some kind of automated job that deletes any rows in a table whose SinkModifiedOn is older than 48 hours
I assume that they are external tables because they are presenting the contents of the CSV files located in the corresponding storage account.
I need to create something that automatically deletes all rows from that synapse environment that are older than 48 hours, basically implementing the code shown below and I need it to run once per day.
DELETE FROM [dbo].[cpp_job] WHERE [SinkModifiedOn] >= DATEADD(HOUR, -48, GETDATE())
What do I need to create exactly do to this?
Do I need to create something that deletes the CSV files located in the corresponding storage account?
How do I determine ensure whatever "thing" I create to do the deletion, is actually deleting everything older than 48 hours?
Any help of recommendations would be greatly appreciated.
All responses (
Answers (
