Master Data Services (MDS) error ERR210055 and how to solve it

If you are me you try to do things differently, most of the time they work out on this occasion something went wrong.

MDS Interface does not let you change the length of an member (Free Text) once you have created. If you know the structure of MDS and how it works you can go to the table in this case tbl_3_38 indentify the column name by using the following query

SELECT a.Name, a.TableColumn, a.DataTypeInformation,*

FROM [mdm].[tblAttribute] a

INNER JOIN mdm.tblEntity e ON a.Entity_ID = e.ID

WHERE e.Name = 'Product'

AND e.Model_ID = 3

Make the changes to the table and the [mdm].[tblAttribute] row and rebuild all your views including viw_SYSTEM_3_38_CHILDATTRIBUTES

Here is where the fun starts when I tried to stage the data I get the following error

Error: ERR210055

Description: An unknown error occurred when staging member

record.

Tips for fixing the issue: If an unhandled exception occurs during the staging

process, all records will be marked with this error.

This error may have nothing to do with records that

display this error.

Against each row, nice. Problem solving time what went wrong.

Find the stored proc that MDS is using that generated the error by doing a text search against all the stored procs in MDS database and the result is udpStagingMemberSave.

Next I had to add the following to code

SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage;

to the mdm.udpStagingMemberSave to display the error.

2601       Cannot insert duplicate key row in object 'mdm.tbl_3_38_EN' with unique index 'ix_tbl_3_38_EN_Version_ID_VersionMember_ID'. The duplicate key value is (3, ).

You will have to run mdm.udpStagingMemberSave manually otherwise you will not see the result.

Helpful sql

UPDATE [RegDistMDS].[mdm].[tblStgMember] SET Status_ID = 0, ErrorCode =''

EXEC mdm.udpStagingMemberSave 1, --@User_ID admin user

                              3, --@Version_ID,

                              2, --@LogFlag Defualt

                              39 --@Batch_ID the last one processed

Eventually you find this dynamic sql is causing the error. I’ve highlighted what is causing the issue.

                     INSERT INTO mdm.' + quotename(@Entity_Table) + N'  

                        ( 

                             Version_ID  

                            ,VersionMember_ID 

                            ,AsOf_ID --Use this column to map between new @@IDENTITY and Stage_ID within OUTPUT clause 

  

                            ,Status_ID 

                            ,ValidationStatus_ID 

                            ,Name 

                            ,Code 

                            ,EnterDTM 

                            ,EnterUserID 

                            ,EnterVersionID 

                            ,LastChgDTM 

                            ,LastChgUserID 

                            ,LastChgVersionID 

                            ' + CASE @MemberType_ID  

                                    WHEN @MemberTypeCons THEN N',Hierarchy_ID'  

                                    WHEN @MemberTypeColl THEN N',Owner_ID' 

                                    ELSE N'' 

                                END + N' 

                        ) 

                        OUTPUT inserted.AsOf_ID, inserted.ID INTO #tblMemberID(Stage_ID, ID) 

                        SELECT 

                             ' + @strVersion_ID + N' --Version_ID                          

                            ,NULL -- Set the default Member_ID 

                            ,Stage_ID --AsOf_ID  

                            ,1 --Status_ID 

                            ' + CASE @MemberType_ID  

                                    WHEN @MemberTypeColl THEN N',3 --Set ValidationStatus_ID TO 3 (validation succeeded) for collection members since business rules does not apply ' 

                                    ELSE N',0 --Set ValidationStatus_ID to New AwaitingValidation' 

                                 END + N'     

                            ,Member_Name 

                            ,Member_Code 

                            ,GETUTCDATE() --EnterDTM 

                            ,' + @strUser_ID + N' --EnterUserID 

                            ,' + @strVersion_ID + N' --EnterVersionID 

                            ,GETUTCDATE() --LastChgDTM 

                            ,' + @strUser_ID + N' --LastChgUserID 

                            ,' + @strVersion_ID + N' --LastChgVersionID 

                            ' + CASE @MemberType_ID  

                                    WHEN @MemberTypeCons THEN N',' + ISNULL(@strHierarchy_ID, N'NULL --Hierarchy_ID') 

                                    WHEN @MemberTypeColl THEN N',' + @strUser_ID + N' --Owner_ID' 

                                    ELSE N'' 

                                END + N' 

                        FROM #tblStage  

                        WHERE Status_ID = @StatusOK;

So eventually you will find that that the following index is to blame.

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mdm].[tbl_3_38_EN]') AND name = N'ix_tbl_3_38_EN_Version_ID_VersionMember_ID')

CREATE UNIQUE NONCLUSTERED INDEX [ix_tbl_3_38_EN_Version_ID_VersionMember_ID] ON [mdm].[tbl_3_38_EN]

(

      [Version_ID] ASC,

      [VersionMember_ID] ASC

)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, DATA_COMPRESSION = PAGE) ON [PRIMARY]

GO

Somehow when I used the SSMS in design mode to change the table it overwrote the index incorrectly. Yeah so much joy

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mdm].[tbl_3_38_EN]') AND name = N'ix_tbl_3_38_EN_Version_ID_VersionMember_ID')

CREATE UNIQUE NONCLUSTERED INDEX [ix_tbl_3_38_EN_Version_ID_VersionMember_ID] ON [mdm].[tbl_3_38_EN]

(

      [Version_ID] ASC,

      [VersionMember_ID] ASC

)

WHERE ([VersionMember_ID] IS NOT NULL)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, DATA_COMPRESSION = PAGE) ON [PRIMARY]

GO