SQL 2012 SP1 schema version 11.1.0.0 Staging Stored Proc for Entity returns SYSERR207|Invalid column name

SQL 2012 SP1 schema version 11.1.0.0 Staging Stored Proc for Entity returns SYSERR207|Invalid column name by Olof Szymczak

Resolved

as Duplicate Help for as Duplicate

Type:	Bug
ID:	772179
Opened:	11/25/2012 8:31:05 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Hi,

If you have change tracking enable on the Attribute Code or Name an error is thrown when executing the staging entity load stored proc
udp_[Model]_[EntityName]_Leaf

This was working in previous versions

The code that is casusing an issue is
SELECT DISTINCT ID, Name, ChangeTrackingGroup FROM mdm.tblAttribute WHERE Entity_ID = 145 AND ChangeTrackingGroup > 0 AND MemberType_ID = 1
...
...
...
-- Update change tracking mask.
-- This should be done before the values in EN table is updated.
SET @TrackGroupCount = 1;

WHILE @TrackGroupCount <= @TrackGroupMax BEGIN
    SELECT
        @ChangeTrackingGroup = ChangeTrackingID,
        @ChangedAttributeName = AttributeName,
        @EntityAttributeName = N'uda_145_' + CONVERT(NVARCHAR(30), AttributeID)
    FROM @TABLECTG
    WHERE ID = @TrackGroupCount;

    -- Update change tracking mask for merge optimistic.
    SET @SQLCTG = N'
    UPDATE mdm.[tbl_5_145_EN]
    SET ChangeTrackingMask = ISNULL(ChangeTrackingMask, 0) | ISNULL(POWER(2,@ChangeTrackingGroup -1), 0)
    FROM mdm.[tbl_5_145_EN] en
        INNER JOIN [stg].[Portal_Config_Leaf] stgl
        ON en.Code = stgl.Code AND en.Version_ID = @Version_ID
        AND stgl.ImportStatus_ID = @ImportStatus_ID
        AND stgl.Batch_ID = @Batch_ID
        AND stgl.ImportType = @ImportType
        AND stgl.' + quotename(@ChangedAttributeName) + N' IS NOT NULL
        AND COALESCE(NULLIF(stgl.' + quotename(@ChangedAttributeName) + N', en.' + quotename(@EntityAttributeName) + N'), NULLIF(en.' + quotename(@EntityAttributeName) + N', stgl.' + quotename(@ChangedAttributeName) + N')) IS NOT NULL;
        ';

This line can not work as there is no user defined attribute for code or name
@EntityAttributeName = N'uda_145_' + CONVERT(NVARCHAR(30), AttributeID)

Details (expand)

English

SQL Server 2012 - Developer Edition

Master Data Services

Not Applicable

Create Model: SandBox
Create Entity: ChangeTrackingTest,
Staging Table: SandBox_ChangeTrackingTest
Create Code values Automatically: Yes
Start with: 1
Enable explicit hierarchies and collections: No

Add Attribute
ChangeTrackingTest.TestAttribute
Free-From
DataType: Text
Enable change tracking: Yes
Change tracking group 1

Edit
ChangeTrackingTest.Code
Enable change tracking: Yes

Run the following script
DECLARE @BatchTag NVARCHAR(50)
SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
INSERT INTO [stg].[SandBox_ChangeTrackingTest_Leaf]
(
    [ImportType]
    ,[ImportStatus_ID]
    ,[BatchTag]
    ,[Code]
    ,[Name]
)
SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', '8' AS 'Code', 'Test' AS 'Name'

EXEC stg.udp_SandBox_ChangeTrackingTest_Leaf 'VERSION_1', 0, @BatchTag

Msg 50000, Level 16, State 1, Procedure udp_SandBox_ChangeTrackingTest_Leaf, Line 1358
SYSERR207|Invalid column name 'uda_175_3687'.

no error

X64

0 attachments

The staging stored procs need to be changed from or something similar. This is the least amount of code change to make it work
SELECT
        @ChangeTrackingGroup = ChangeTrackingID,
        @ChangedAttributeName = AttributeName,
        @EntityAttributeName = N'uda_145_' + CONVERT(NVARCHAR(30), AttributeID) END
    FROM @TABLECTG
    WHERE ID = @TrackGroupCount;

To

SELECT
        @ChangeTrackingGroup = ChangeTrackingID,
        @ChangedAttributeName = AttributeName,
        @EntityAttributeName = CASE WHEN AttributeName = N'Code' THEN N'Code'
                                    WHEN AttributeName = N'Name' THEN N'Name'
                                    ELSE N'uda_145_' + CONVERT(NVARCHAR(30), AttributeID) END
    FROM @TABLECTG
    WHERE ID = @TrackGroupCount;

So change in [mdm].[udpEntityStagingCreateLeafStoredProcedure]
SELECT
    @ChangeTrackingGroup = ChangeTrackingID,
    @ChangedAttributeName = AttributeName,
    @EntityAttributeName = N''uda_' + CONVERT(NVARCHAR(30), @Entity_ID) + N'_'' + CONVERT(NVARCHAR(30), AttributeID)
FROM @TABLECTG
WHERE ID = @TrackGroupCount;

To

SELECT
    @ChangeTrackingGroup = ChangeTrackingID,
    @ChangedAttributeName = AttributeName,
    @EntityAttributeName = CASE WHEN AttributeName = N''Code'' THEN N''Code''
                                WHEN AttributeName = N''Name'' THEN N''Name''
                                ELSE N''uda_' + CONVERT(NVARCHAR(30), @Entity_ID) + N'_'' + CONVERT(NVARCHAR(30), AttributeID) END
FROM @TABLECTG
WHERE ID = @TrackGroupCount;