The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
@Anonymous did you ever get a resolution to this? I am experiencing a similar issue. Thanks!
Hi @Anonymous you may be better off if you just drop and recreate the staging and fact table as part of your load. The DELETE overhead probably isn't worth it if you want the tables empty when you start inserting
Hello @Anonymous ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help .
Hi @Anonymous ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help .
Hi @Anonymous ,
Thanks for using Fabric Community.
As I understand it looks like an intermittent issue.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.