Hi all
I have the following error showing in Management Reporter Service Log. Running AX 2012, and Management Reporter CU13.
Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]' due to trigger '[Reporting].[SecurityPrincipal_Insert_UniqueName]'.
The transaction ended in the trigger. The batch has been aborted.
Can anyone point me to what this issue may relate to please and how I might fix it?
Thanks
James
*This post is locked for comments
Fixed. Well maybe....
We got both the MR error eliminated and also the DataMart refreshed.
we had a great deal of trouble getting the initial DataMart integration to complete.
Turns out the MR error was preventing the DataMart from completing its Initial integration.
What we found
The AX/MR service account needed to have the SecurityAdmin role applied, it only had SystemAdmin and SystemUser.
Since I use my named account to run the MR console I Also applied this to my named account.
The client was doing a security audit. I suspect they did not know any better and so...
We also needed to temporarily disable the Trigger that was causing the Error message. The trigger was tripping and threw up the error to the event logs.
“Cannot insert duplicate key row in object '[Reporting].[SecurityPrincipal]' due to trigger [Reporting].[SecurityPrincipal_Insert_UniqueName]”
Disabling the trigger appears to have accomplished two things:
The Datamart was able to complete it Initial integration.
And it allowed several user accounts to be populated into the MR database (SecurityPrinciple) table.
So it would appear the Error was preventing the MR users list from updating.
select * from [managementreporter].[reporting].SecurityPriciple
We later re-enable the trigger and we are getting no more MR errors.
In Report Designer the list of users should now agree with the role memberships in AX and with the SecurityPrinciple table.
We are concluding that there must have been something that tripped the trigger. Maybe a duplicate entry. Maybe caused by permissions issues (axprod was unable to complete some function correctly?).
We will have the client create a new AX user, and assign them to some report writer role or their new custom roles and see if the table update takes place. And see if the Trigger again throws an MR Error.
Got lots of help with this so thanks to everyone!!!
-Damian
Hey James,
We have exactly the same problem. Same MR cu13. Same error.
Aside from the error message were there any functional problems as far as you could tell?
Did you have any luck figuring out how to correct this?
I tried dropping and rebuilding the datamart. And the error still persists.
This is the Trigger in table Reporting.SecurityPrinciple at issue
USE [ManagementReporter]
GO
/****** Object: Trigger [Reporting].[SecurityPrincipal_Insert_UniqueName] Script Date: 3/14/2018 6:14:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Reporting].[SecurityPrincipal_Insert_UniqueName]
ON [Reporting].[SecurityPrincipal]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @DuplicateName nvarchar(255)
SELECT TOP 1 @DuplicateName = i.[Name] FROM inserted i INNER JOIN [Reporting].[SecurityPrincipal] sp ON sp.[Name] = i.[Name] AND sp.[ID] <> i.[ID] AND sp.[IsDeleted] = 0
IF (@DuplicateName IS NOT NULL) BEGIN
RAISERROR(52601, 16, 0, '[Reporting].[SecurityPrincipal]', '[Reporting].[SecurityPrincipal_Insert_UniqueName]') --This needs to be a THROW for azure
ROLLBACK TRANSACTION
END
END
GO
Thanks
-Damian
Hey James,
We have exactly the same problem. Same MR cu13. Same error.
Aside from the error message were there any functional problems as far as you could tell?
Did you have any luck figuring out how to correct this?
I tried dropping and rebuilding the datamart. And the error still persists.
This is the Trigger in table Reporting.SecurityPrinciple at issue
USE [ManagementReporter]
GO
/****** Object: Trigger [Reporting].[SecurityPrincipal_Insert_UniqueName] Script Date: 3/14/2018 6:14:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Reporting].[SecurityPrincipal_Insert_UniqueName]
ON [Reporting].[SecurityPrincipal]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @DuplicateName nvarchar(255)
SELECT TOP 1 @DuplicateName = i.[Name] FROM inserted i INNER JOIN [Reporting].[SecurityPrincipal] sp ON sp.[Name] = i.[Name] AND sp.[ID] <> i.[ID] AND sp.[IsDeleted] = 0
IF (@DuplicateName IS NOT NULL) BEGIN
RAISERROR(52601, 16, 0, '[Reporting].[SecurityPrincipal]', '[Reporting].[SecurityPrincipal_Insert_UniqueName]') --This needs to be a THROW for azure
ROLLBACK TRANSACTION
END
END
GO
Thanks
-Damian
Hi James,
We are facing the exact issue, please let us know how you overcame this issue. Thanks in Advance
Hi James Terrington,
Please refer to this old thread with similar question community.dynamics.com/.../182625
#1
André Arnaud de Cal...
291,391
Super User 2024 Season 2
#2
Martin Dráb
230,445
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
