udpEntityStagingCreateLeafStoredProcedure Create Code values automatically Error - by Olof Szymczak

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 751331 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 6/28/2012 9:11:45 AM
Access Restriction Public

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
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 Matthew [MSFT] 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