locked
Workflow cannot update External List Item RRS feed

  • Question

  • Hi there,

    I have a SP2010 workflow on a SP List which is meant to update 2 fields in an External List. However it fails with the message "The workflow could not update the item in the external data source. Make sure the user has permissions to access the external data source and update items."

    However, I have a 2nd workflow on that same SP List that creates a new item in that same External List. That works fine. This proves that BCS - Secure Store is set-up correctly, I think?

    I can also create or update items directly in the External List (from within SP), which again seems to confirm that BCS - Secure Store is set correctly and also that permissions on SQL server are set correctly.

    The 2 fields being updated are of type "Single line of text" in SP List and nvarchar in External List (SQL), so they seem to match OK.

    The "join" for the update is made on the ID field in the SP List and the ID field in the External List.

    These are both very large lists (approx. 30,000 items) and I'm wondering if this is causing the update issue? UPDATE: I've tested on 2 very small lists with the same result so the list size isn't the issue.

    I'd appreciate any thoughts on how I can debug this workflow and get my updates working.

    Mel


    • Edited by MelHowes Friday, August 9, 2013 12:06 PM
    Friday, August 9, 2013 11:03 AM

Answers

  • Hi Daniel,

    Just to clarify, one workflow can "Create a new list item" in the external list but the second workflow cannot "Update a list item" in the external list. As a test I also set up a third workflow to "Delete a list item" in the external list, and that one works fine too.

    The issue seems to be only with the "Update a list item" action.

    It is the same user running all 3 workflows on the same External Content Type. There is no "Impersonation step" in any of the workflows, the impersonation is set on the ECT where the Secure Store Target ID is referenced (and so it is the same for all 3 workflows). 

    The trigger for the Create workflow is when a new item is created and for the Update workflow it is when an item is changed.

    Having tested the same workflows on an internal SP List there was no problem. I think maybe there is an issue with the Update function for ECTs?

    As a workaround I am now running one workflow that creates a new item and deletes the old item in the external list every time an item is updated. It works but seems like an inefficient approach.

    Cheers,

    Mel

    • Proposed as answer by ansh4tyBanned Wednesday, August 14, 2013 9:10 AM
    • Marked as answer by star.wars Monday, August 19, 2013 2:12 AM
    Monday, August 12, 2013 11:24 AM
  • Hi Mel,

    your situation is quite unique, as you already tried to your internal and have no issue, if i may ask is there any difference between the user permissions at internal and the issued-environment?

    as i know, to create, edit and delete permission is different each one to another, so for example a person can only create, but cant edit and delete, or do the permutation action from it.

    It seems you set the impersonation method similar with this example:

    http://petersullivan.com.au/2010/12/15/the-workflow-could-not-update-the-item-in-the-external-data-source-make-sure-the-user-has-permissions-to-access-the-external-data-source-and-update-items/

    to try, perhaps you may use another domain account that have all the permissions.

    please have a check on this article to set the permissions for external account:

    http://technet.microsoft.com/en-us/library/ee524069.aspx

    if should everything is already double checked, then we re-do the process.

    another method that you may try, is also to put the impersonation step on the workflow, as daniel post before:

    https://www.nothingbutsharepoint.com/sites/eusp/pages/impersonation-in-sharepoint-workflows---an-interesting-pitfall.aspx


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by ansh4tyBanned Wednesday, August 14, 2013 9:10 AM
    • Marked as answer by star.wars Monday, August 19, 2013 2:12 AM
    Wednesday, August 14, 2013 8:55 AM

All replies

  • Hi Mel,

    I understand that two workflows are attached on the same external list, one can update the the list field, but another cannot.

    Please also compare the differences between two workflows, check if the problematic workflow has "Impersonation step" in SharePoint Designer, if it has, please check if the workflow author has proper permissions on the "External content type" object from BCS service application.

    Please check if SharePoint administrator could trigger the problematic workflow to update the external list item, if only some particular users cannot use the workflow to update the external list field, you may need to check if these users have proper permissions on the external content type from BCS service application.

    Thanks


    Daniel Yang
    TechNet Community Support

    Monday, August 12, 2013 9:54 AM
  • Hi Daniel,

    Just to clarify, one workflow can "Create a new list item" in the external list but the second workflow cannot "Update a list item" in the external list. As a test I also set up a third workflow to "Delete a list item" in the external list, and that one works fine too.

    The issue seems to be only with the "Update a list item" action.

    It is the same user running all 3 workflows on the same External Content Type. There is no "Impersonation step" in any of the workflows, the impersonation is set on the ECT where the Secure Store Target ID is referenced (and so it is the same for all 3 workflows). 

    The trigger for the Create workflow is when a new item is created and for the Update workflow it is when an item is changed.

    Having tested the same workflows on an internal SP List there was no problem. I think maybe there is an issue with the Update function for ECTs?

    As a workaround I am now running one workflow that creates a new item and deletes the old item in the external list every time an item is updated. It works but seems like an inefficient approach.

    Cheers,

    Mel

    • Proposed as answer by ansh4tyBanned Wednesday, August 14, 2013 9:10 AM
    • Marked as answer by star.wars Monday, August 19, 2013 2:12 AM
    Monday, August 12, 2013 11:24 AM
  • Hi Mel,

    your situation is quite unique, as you already tried to your internal and have no issue, if i may ask is there any difference between the user permissions at internal and the issued-environment?

    as i know, to create, edit and delete permission is different each one to another, so for example a person can only create, but cant edit and delete, or do the permutation action from it.

    It seems you set the impersonation method similar with this example:

    http://petersullivan.com.au/2010/12/15/the-workflow-could-not-update-the-item-in-the-external-data-source-make-sure-the-user-has-permissions-to-access-the-external-data-source-and-update-items/

    to try, perhaps you may use another domain account that have all the permissions.

    please have a check on this article to set the permissions for external account:

    http://technet.microsoft.com/en-us/library/ee524069.aspx

    if should everything is already double checked, then we re-do the process.

    another method that you may try, is also to put the impersonation step on the workflow, as daniel post before:

    https://www.nothingbutsharepoint.com/sites/eusp/pages/impersonation-in-sharepoint-workflows---an-interesting-pitfall.aspx


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by ansh4tyBanned Wednesday, August 14, 2013 9:10 AM
    • Marked as answer by star.wars Monday, August 19, 2013 2:12 AM
    Wednesday, August 14, 2013 8:55 AM
  • Hi.

    I have the same issue. Server: SP 2013 enterprise.

    I connect to an SQL DB, via Secure Store, and external list created in BCS. I created all CRUD operations. The user specified in Secure Store has Read, Write and DBowner access.

    The web app pool account has Execute and client acess in BCD object permission settings. (it is this account that runs the workflow)

    It works great! I create, read and deletes the items in the external list. I use a SP2010 workflow triggered from an 2013 workflow. It also work if I run the sp2010 workflow manually.

    Except: The Update Item in <external list>. It fails!! Same error as specified in the first post.

    I am going crazy here.... I have checked all the settings and the links from Aries. No luck.Did you solve the problem?

    Tuesday, January 28, 2014 12:51 PM
  • Hi,

    This is still an issue for me. At this stage I believe the issue to lie within the workflow Update functionality. I'm hoping that by upgrading to the SP2013 Workflow Manager that this will be solved but it'll be another couple of months before I can upgrade.

    Did you ever find a fix?

    Mel

    Tuesday, July 8, 2014 4:34 PM
  • Hi Mel,

    I never found a fix, but decided to spend my time to create a workaround.

    In my case, it was not important to update the same row in the DB. So i decided to create a new row instead and copy a unique id from the first row I created.I can do this because the software using the DB will look for new rows (or changes) and then do an action based on the unique id.

    Hope you find a solution, and post it here when you have :)

    Per Ove


    ---- Per Ove

    Wednesday, July 9, 2014 8:16 AM
  • Same issue in 2013 - can NOT update external list item. All other actions working fine.

    Have tried all another methods...

    BUT!!! SP2010 properly works with the same parameters. It prevents me from upgrading to SP2013.

    Thursday, July 31, 2014 7:23 AM