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
