Home Dashboard Directory Help
Search

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


Status: 

Resolved
 as Duplicate Help for as Duplicate


4
0
Sign in
to vote
Type: Bug
ID: 772179
Opened: 11/25/2012 8:31:05 PM
Access Restriction: Public
1
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
Posted by EthernetIP on 10/1/2013 at 1:27 PM
FYI
Version : 11.0.3128.0
with bug
Posted by EthernetIP on 10/1/2013 at 1:16 PM
Umm,
Where is the solution ?
Microsoft says its a Duplicate "Hi Olof, Thanks for the feedback. We’re closing this issue as “Duplicate” as we have an existing work item tracking this problem. We have diagnosed the root cause and expect to release a hotfix shortly. Matthew"
If so maybe someone at Microsoft can also point to it.
My search for SYSERR207 turns up only this thread.
And yes we also have this issue. This has been open for a while now. Thank you.
Posted by yes please on 9/24/2013 at 4:08 PM
Microsoft,

If this is a duplicate, then can you provide the link to the other connect item so I can watch the issue for a hotfix please?
Posted by MSIT Justin Li on 5/27/2013 at 8:07 AM
Watching the issue as I encounter the same issue on SQL Server 2012 SP1 also. The version number is 11.0.3000. When the fix would be ready?
Posted by SwapnilKothari on 2/8/2013 at 9:50 AM
Which Release has got this issue Resolved?

Currently I have tried using the Release SP1 CU1 and I was getting the same exception for the staging process for an entity which had attributes (with change tracking enabled).

Could you please confirm which release has got this issue fixed?

Also, could you please provide the issue# which is "duplicate" of the above error?

Thanks,
Swapnil
Posted by Microsoft on 12/3/2012 at 11:00 AM
Hi Olof,

Thanks for the feedback. We’re closing this issue as “Duplicate” as we have an existing work item tracking this problem. We have diagnosed the root cause and expect to release a hotfix shortly.

Matthew
Sign in to post a workaround.
Posted by Olof Szymczak on 11/25/2012 at 10:24 PM
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;