udpEntityStagingCreateLeafStoredProcedure Create Code values automatically Error by Olof Szymczak

Hi

There is a bug in the stored proc when an Entity has been marked for "Create Code Values Automatically". If you insert a member and one of the attributes is a domain attribute, that doesn't exist. you will get an error of

Msg 515, Level 16, State 2, Procedure udp_EntitySample_Leaf, Line 335
Cannot insert the value NULL into column 'Code', table 'MDS.mdm.tblStgErrorDetail'; column does not allow nulls. INSERT fails.

so investigating stg.udp_EntitySample_Leaf, I find the sql code that is causing the error

-- Error 210003 The attribute value references a member that does not exist or is inactive. Binary Location 2^2:
UPDATE stgl
SET ErrorCode = IsNull(ErrorCode,0) | 4
    OUTPUT inserted.Batch_ID, inserted.Code, N'[Channel]', inserted.[Channel], 210003
    INTO [mdm].[tblStgErrorDetail] (Batch_ID, Code, AttributeName, AttributeValue, UniqueErrorCode)
FROM [stg].[MTN_ForecastRange_Leaf] stgl
    LEFT OUTER JOIN mdm.tbl_2_11_EN dm
    ON stgl.[Channel] = dm.Code
    AND dm.Version_ID = @Version_ID AND dm.Status_ID = @MemberStatusOK
WHERE stgl.ImportType NOT IN (@IT_Delete, @IT_Purge, @IT_DeleteSetNullToRef, @IT_PurgeSetNullToRef)
AND LEN(COALESCE(NULLIF(stgl.[Channel], @NULLText), N'')) > 0
AND stgl.Batch_ID = @Batch_ID AND stgl.ImportStatus_ID = @StatusDefault AND dm.Code IS NULL

What is happening is that the code is null when trying to insert into [mdm].[tblStgErrorDetail] and the reason for it been null is that it hasn't been generated yet.

So looking the stored proc udpEntityStagingCreateLeafStoredProcedure
Line 544 is where the domain checking code is (error is thrown)

            SET @SQLDBACheck += @TruncationGuard + N'
-- Error 210003 The attribute value references a member that does not exist or is inactive. Binary Location 2^2:
UPDATE stgl
SET ErrorCode = IsNull(ErrorCode,0) | 4
    OUTPUT inserted.Batch_ID, inserted.Code, N''' + @CurrentViewColumn + N''', inserted.' + @CurrentViewColumn + N', 210003
    INTO [mdm].[tblStgErrorDetail] (Batch_ID, Code, AttributeName, AttributeValue, UniqueErrorCode)
FROM [stg].' + @StagingLeafTable + N' stgl
    LEFT OUTER JOIN mdm.' + @CurrentDomainTable + N' dm
    ON stgl.' + @CurrentViewColumn + N' = dm.Code
    AND dm.Version_ID = @Version_ID AND dm.Status_ID = @MemberStatusOK
WHERE stgl.ImportType NOT IN (@IT_Delete, @IT_Purge, @IT_DeleteSetNullToRef, @IT_PurgeSetNullToRef)
AND LEN(COALESCE(NULLIF(stgl.' + @CurrentViewColumn + N', @NULLText), N'''')) > 0
AND stgl.Batch_ID = @Batch_ID AND stgl.ImportStatus_ID = @StatusDefault AND dm.Code IS NULL
'
Line 738 Is where you start to auto generate the code values
--If code generation is allowed populate new codes into the staging table.
...
...

Olof