none
provisioning to multiple tables using a View and SQL MA RRS feed

  • Question

  • I think I know the answer to this question, but asking for confirmation.

    Is it true that the SQL MA (out of the box) cannot provision to a view of multiple tables? 

    Consider this test...I have an ACCOUNTS table with columns ACCOUNT (VARCHAR), DOMAIN_ID(INT), and a DOMAINS table with DOMAIN_ID(INT) and DOMAIN(VARCHAR).

    I created a View called DOMAIN_ACCOUNTS that goes something like...

    SELECT     Domains.Domain, Accounts.Account
    FROM         Domains INNER JOIN Accounts ON Domains.Domain_ID = Accounts.Domain_Id

    FIM Portal is configured to send accountname and domain to the SQL view.

    When I reference this view while configuring the SQL MA, it doesn't complain.  And the entire flow seems to work up until the SQL MA export at which point I get the error: permission-issue, View or function 'domain_view' is not updatable because the modification affects multiple base tables.

    As a way to test 'the possible' - is there anyway to trick the agent into just sending the values anyway such that I could use a trigger with INSTEAD OF INSERT and intercept the domain value so I can write it myself with an SP?  Looking for workaround suggestions if any are out there.

    Thanks!

    Wednesday, January 9, 2013 10:31 PM

Answers

  • Well, your view would need to be an updatable view, which would follow the standard SQL rules for what constitutes an updatable view - in this case, since the Domain_ID is not present in the view, I would imagine it's not updatable. Try adding that attribute into the view... but again, may not work if the Domain_ID is auto-indexed (ie, you may need to provide a value). Been a while since I've worked with updatable views... they're generally considered a no-no.

    Essentially, if you can run an INSERT statement against your view in SQL Server Management Studio, I believe you can export to it from the FIM SQL MA. I could be wrong.

    Your alternative would be to have two separate MA's... but I suspect you don't want to create a new record in the Domains table on each export anyway... all you really want to do is create a new account,Domain_ID pair, right?

    Try adding the account.domain_ID field into the view, writing the domain_ID and account values out, then the domains.domain should populate on import... make sense?


    FIMSpecialist.com | MCTS: FIM 2010

    Wednesday, January 9, 2013 11:04 PM
  • Hi Osho

    Mostly SQL Server Management Console is not even able to update the Values in the views comming from multiple tables. It you can edit all the values in the view from SQL Server Management Console , there is a fair chance FIM 2010 will do it as well.

    I would suggest that you create another table same as the view and place your triggers on that table. and configure SQL MA to export entries to that table.

    You can use the following logic before the runs

    To create the table

    SELECT * INTO NEW_TABLE FROM VIEW_NAME

    before running the run profile

    TRUNCATE TABLE NEW_TABLE

    INSERT INTO NEW_TABLE SELECT * FROM VIEW_NAME

    this will make sure the table always have the latest data

    Hope this helps.



    Thursday, January 10, 2013 6:47 AM
  • I've accomplished this in the past by adding INSTEAD OF triggers to the view to handle the changes.


    My Book - Active Directory, 4th Edition
    My Blog - www.briandesmond.com

    Saturday, January 12, 2013 5:34 AM
    Moderator

All replies

  • Well, your view would need to be an updatable view, which would follow the standard SQL rules for what constitutes an updatable view - in this case, since the Domain_ID is not present in the view, I would imagine it's not updatable. Try adding that attribute into the view... but again, may not work if the Domain_ID is auto-indexed (ie, you may need to provide a value). Been a while since I've worked with updatable views... they're generally considered a no-no.

    Essentially, if you can run an INSERT statement against your view in SQL Server Management Studio, I believe you can export to it from the FIM SQL MA. I could be wrong.

    Your alternative would be to have two separate MA's... but I suspect you don't want to create a new record in the Domains table on each export anyway... all you really want to do is create a new account,Domain_ID pair, right?

    Try adding the account.domain_ID field into the view, writing the domain_ID and account values out, then the domains.domain should populate on import... make sense?


    FIMSpecialist.com | MCTS: FIM 2010

    Wednesday, January 9, 2013 11:04 PM
  • Hi Osho

    Mostly SQL Server Management Console is not even able to update the Values in the views comming from multiple tables. It you can edit all the values in the view from SQL Server Management Console , there is a fair chance FIM 2010 will do it as well.

    I would suggest that you create another table same as the view and place your triggers on that table. and configure SQL MA to export entries to that table.

    You can use the following logic before the runs

    To create the table

    SELECT * INTO NEW_TABLE FROM VIEW_NAME

    before running the run profile

    TRUNCATE TABLE NEW_TABLE

    INSERT INTO NEW_TABLE SELECT * FROM VIEW_NAME

    this will make sure the table always have the latest data

    Hope this helps.



    Thursday, January 10, 2013 6:47 AM
  • I've accomplished this in the past by adding INSTEAD OF triggers to the view to handle the changes.


    My Book - Active Directory, 4th Edition
    My Blog - www.briandesmond.com

    Saturday, January 12, 2013 5:34 AM
    Moderator