none
Generic SQL Connector: issues when exporting deletion of multivalue attribute (e.g. removal of last group member) RRS feed

  • Question

  • Hi.

    I'm running MIM Sync (v4.5.412.0) with the newest release of Generic SQL Connector (v.1.1.953.0). I have a non-complex setup in the Microsoft SQL database:

    [Users] table
    [Groups] table
    [GroupsMV] table (used for 'MemberID' multivalue attribute in Group objects - basically storing group memberships)

    All imports/exports are done with Table operation (so no custom SQL statements or Stored Procedures). Exporting AD users, AD groups and additions/removals of memberships to the SQL database works perfect - except for one thing:

    If the last member(s) in a group is removed, export to SQL is not working doing what it should. No errors during connector Export, but an "exported-change-not-reimported" error during re-import. If I look in the GroupsMV table, no records were deleted during export.

    Running a SQL Trace I can see the following (one working example, one error example):

    -----------------------------

    When removing 2 of 4 members from 'MemberID' multivalue attribute on Group 1 (works correctly):

    exec sp_executesql N'SELECT COUNT(*) 
    FROM GROUPSMV
    WHERE  (  ( MemberID = @P1 )  AND  ( GROUP_ID = @P2 )  ) ;',N'@P1 nvarchar(36),@P2 nvarchar(36)',N'018e67c1-677c-4296-8464-9a91f47b98dc',N'b02d9a85-f405-49a6-8c37-6d9d5eea4288'

    exec sp_executesql N'DELETE FROM GROUPSMV
    WHERE  (  ( MemberID = @P1 )  AND  ( GROUP_ID = @P2 )  ) ;',N'@P1 nvarchar(36),@P2 nvarchar(36)',N'018e67c1-677c-4296-8464-9a91f47b98dc',N'b02d9a85-f405-49a6-8c37-6d9d5eea4288'

    exec sp_executesql N'SELECT COUNT(*) 
    FROM GROUPSMV
    WHERE  (  ( MemberID = @P1 )  AND  ( GROUP_ID = @P2 )  ) ;',N'@P1 nvarchar(36),@P2 nvarchar(36)',N'33463cdb-f9d4-44c5-9abb-b4349f69663d',N'b02d9a85-f405-49a6-8c37-6d9d5eea4288'

    exec sp_executesql N'DELETE FROM GROUPSMV
    WHERE  (  ( MemberID = @P1 )  AND  ( GROUP_ID = @P2 )  ) ;',N'@P1 nvarchar(36),@P2 nvarchar(36)',N'33463cdb-f9d4-44c5-9abb-b4349f69663d',N'b02d9a85-f405-49a6-8c37-6d9d5eea4288'

    -----------------------------

    When removing last 2 members from 'MemberID' multivalue attribute on Group 1 (error, records are not removed in MV-table):

    exec sp_executesql N'SELECT COUNT(*) 
    FROM GROUPSMV
    WHERE  ( GROUP_ID = @P1 ) ;',N'@P1 nvarchar(36)',N'b02d9a85-f405-49a6-8c37-6d9d5eea4288'

    exec sp_executesql N'DELETE FROM GROUPSMV
    WHERE  ( GROUP_ID = @P1 ) ;',N'@P1 nvarchar(2)',NULL

    -----------------------------

    The last delete statement seems to be lacking the GROUP_ID value, as it's NULL. But we can clearly see that it's the correct value in the select statement. As far as I understand, it should work correctly by deleting all memberships for specific group if it just weren't for the GROUP_ID being NULL.

    Is this a bug in the Generic SQL Connector? Has anyone seen this behavior in the past, and if so - got a solution or troubleshooting tips?


    Regards,
    Stian S.


    Tuesday, October 22, 2019 6:20 PM

Answers

  • This looks to be a bug in the Generic SQL connector (v.1.1.953.0), so I will go ahead and create a case with Microsoft CSS.
    • Marked as answer by Stian__S Thursday, October 24, 2019 7:04 AM
    • Edited by Stian__S Thursday, October 24, 2019 7:05 AM
    Thursday, October 24, 2019 7:04 AM

All replies

  • Hi,

    I haven't seen this particular error in the past, but we've encountered a number of bugs in the Generic SQL Connector unfortunately.

    But could you please check your Sync rule and ensure that you allow flowing null values to the memberID attribute?

    Br

    Leo


    Did my post help? Please use "Mark as answer" or "Propose as answer". Thank you!

    Wednesday, October 23, 2019 6:36 AM
  • Yes, I've allowed to flow Null to MemberID attribute.
    Wednesday, October 23, 2019 7:20 AM
  • This looks to be a bug in the Generic SQL connector (v.1.1.953.0), so I will go ahead and create a case with Microsoft CSS.
    • Marked as answer by Stian__S Thursday, October 24, 2019 7:04 AM
    • Edited by Stian__S Thursday, October 24, 2019 7:05 AM
    Thursday, October 24, 2019 7:04 AM