Home Dashboard Directory Help
Search

udpEntityStagingCreateLeafStoredProcedure Create Code values automatically Error by Olof Szymczak


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 751331
Opened: 6/28/2012 9:11:45 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Details
Sign in to post a comment.
Posted by JJ78 on 8/6/2013 at 5:05 AM
Can someone confirm that the bug is fixed with SP1??
I run into same problem, my SQL Server is patched for 2012 SP1 (11.0.3000) !
But if run following query for MDS DB:
--> SELECT ProductVersion,SchemaVersion FROM mdm.tblSystem
i get:
ProductVersion=11.0.0.0
SchemaVersion=11.02.1
Is this correct for an SP1 MDS System?, or are there any specific Upgrade Steps for MDS to apply SP1
Posted by Gorm Braarvig on 9/5/2012 at 5:49 AM
Greetings,

I have this at customer site for "Customer"-hub on an enterprise customer.... is there a work-around?
I might use my own code-generator, nut really don't want to...
Posted by Microsoft on 8/8/2012 at 7:56 AM
Hi Olof,

Thanks for the feedback. We have corrected the problem behavior you reported, and the fix should be available in SQL Server 2012 Service Pack 1.

Matthew
Sign in to post a workaround.