Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Problem using UPDATE statement

I have tried both methods as described in the manual to perform the following update:
update SF_TSE_PROD...Document
Set FolderId = '00l70000001CIrWAAW'
where Id ='01570000001KMGTAA4'

returns: "Error 5027 : insufficient access rights on object id".

the other method:
update Openquery(SF_TSE_PROD,
'Select FolderId from Document
Where Id =''01570000001KMGTAA4''')
Set FolderId = '00l70000001CIrWAAW'

returns "Error: The Id column must be selected when OPENQUERY is used in an UPDATE.".

I'm not clear on what's going on. Thanks!
1 person has
this question
+1
Reply
  • As additional detail on the insufficient rights error, I am a full admin on the site in question.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Modify the second method to the following:

    update Openquery(SF_TSE_PROD,
    'Select Id, FolderId from Document
    Where Id =''01570000001KMGTAA4''')
    Set FolderId = '00l70000001CIrWAAW'

    I want to see if this makes the access rights error occur for the 2nd method.

    Also, are you sure that the ids in the statement are in your org ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Also, are you sure that the link server is using your sf id ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • the change that you provided resulted in :
    "Error 5027 : insufficient access rights on object id".

    I accidentally changed my password this morning and had to go and change it in the link server, so my confidence is high that it's using my SF id.

    Thanks!
    --Tim
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • new info!

    the row in the document table that I'm trying to update is in a private folder. Perhaps SF selectively enforces access rights on a per row basis
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • It is salesforce that is complaining.

    Try it as a bulkop and see if the result is different:

    select Id,FolderId, cast('' as nvarchar(255))) as Error
    into Document_Update
    from SF_TSE_PROD...Document
    where Id='01570000001KMGTAA4'

    update Document_Update Set FolderId = '00l70000001CIrWAAW'

    exec sf_bulkops 'Update','SF_TSE_PROD','Document_Update'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Wow, thanks for following up with this. Here is the Message returned
    (1 row(s) affected)

    (1 row(s) affected)
    --- Starting SF_BulkOps for Document_Update
    11:34:50: Run the DBAmp.exe program.
    11:34:50: DBAmp Bulk Operations. V2.13.7 (c) Copyright 2006-2011 forceAmp.com LLC
    11:34:50: Updating Salesforce using Document_Update (WPDWSQL01 / dw_sf_tse_convert) .
    11:34:50: DBAmp is using the SQL Native Client.
    11:34:53: 1 rows read from SQL Table.
    11:34:53: 1 rows failed. See Error column of row for more information.
    11:34:53: 0 rows succesfully processed.
    11:34:53: Errors occurred. See Error column of row for more information.
    11:34:53: Error: DBAmp.exe was unsuccessful.
    11:34:53: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe update Document_Update "WPDWSQL01" "dw_sf_tse_convert" "SF_TSE_PROD"
    --- Ending SF_BulkOps. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_BulkOps, Line 97
    SF_BulkOps Error: 11:34:50: DBAmp Bulk Operations. V2.13.7 (c) Copyright 2006-2011 forceAmp.com LLC11:34:50: Updating Salesforce using Document_Update (WPDWSQL01 / dw_sf_tse_convert) .11:34:50: DBAmp is using the SQL Native Client.11:34:53: 1 rows read from SQL Table.11:34:53: 1 rows failed. See Error column of row for more information.11:34:53: 0 rows succesfully processed.11:34:53: Errors occurred. See Error column of row for more information.

    Here's the row in the Document_Update table:
    Id FolderId Error
    01570000001KMGTAA4 00l70000001CIrWAAW insufficient access rights on object id

    SalesForce REALLY doesn't like you to mess around with the private folders!

    This is coming up because we're splitting our org into two orgs and migrating about 1/4 of the data. (God Bless DBAmp, this would have been a $200,000+ contract outsource without it!!)

    There are a lot of things like Documents that are rows in the doc table that we want to migrate, but their FolderId references a Folder that is not listed in the Folders table (it's their personal folder.) We're having to do massive amounts of Foreign Key and reference key (nchar(18)) 'rekeying' on our transferred records, the hope was that we could change the Document's FolderId to a 'Holding' folder in the new org. The only alternative is to force a hundred users to move hundreds of docs from their personal folders to public folders.

    We've created a rosetta table of all the newly assigned IDs (to their original IDs). We're bulk loading rows into the new org with the Foreign Keys and referenced keys nulled out for the most part, planning to upsert them to their new keys in a second stage.

    We haven't gotten to that rekeying stage in our effort (4 of us locked in a room for a week now.) Hopefully this Access Rights issue will not rear its ugly head on other stuff besides this personal folder issue!

    Thanks again for the help. We couldn't have even considered trying this without DBAmp.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • GOOD NEWS. I created a document in one public folder and used the provided code to successfully move it to another public folder.

    (1 row(s) affected)

    (1 row(s) affected)
    --- Starting SF_BulkOps for Document_Update
    12:01:01: Run the DBAmp.exe program.
    12:01:01: DBAmp Bulk Operations. V2.13.7 (c) Copyright 2006-2011 forceAmp.com LLC
    12:01:01: Updating Salesforce using Document_Update (WPDWSQL01 / dw_sf_tse_convert) .
    12:01:01: DBAmp is using the SQL Native Client.
    12:01:02: 1 rows read from SQL Table.
    12:01:02: 1 rows succesfully processed.
    --- Ending SF_BulkOps. Operation successful.

    So the issue is related to persona folders. I will now create one in my personal folder and see if I get an access issue (to determine if all personal folders cause it or just other people's.)
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited

  • More detail. I created a Document in my own Personal Folder. The code provided worked to move it into a public folder (no access rights issue.) I attempted the original test of a doc in someone else's personal folder and got the access denied response.

    So the problem appears to be constrained to things in personal folders. I am a system admin with api access, I will continue to look for additional possible config options in the SF interface that might somehow extend my privileges.

    Thank you very much for your help!!!!
    --Tim
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly sad, anxious, confused, frustrated indifferent, undecided, unconcerned happy, confident, thankful, excited