I have a stored procedure failing with this error message.
Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 50000. Error Message: proc_create_monthly_kpis failed at: 150, Snapshot isolation transaction aborted due to update conflict. Using snapshot isolation to access table 'stg_monthly_kpis1' directly or indirectly in database 'DMU_Data_Platform_Warehouse' can cause update conflicts if rows in that table have been deleted or updated by another concurrent transaction. Retry the transaction.
Sometimes it works and sometimes it doesn't. There is absolutely no other process running in the Data Warehouse as this is part of an overnight ETL process. The basic stored procedure pattern is as follows
======================================================
BEGIN TRY
BEGIN TRANSACTION
delete from table 'stg_monthly_kpis1'
WHILE LOOP -- loops round month ending dates for the last 5 years
Calculates a set of KPIs for the current month ending date, inserts into stg_monthly_kpis1
There are 10 calculations and insert statements into stg_monthly_kpis1 per loop iteration
END LOOP
delete from my fact table
insert into my fact table select * from stg_monthly_kpis1
COMMIT TRANSACTION
END TRY
==========================
It is the last insert into the fact table that fails
Thanks for your help
