You’re offline. This is a read only version of the page.
1,579
I 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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (