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

Unable to Replicate ContentNote Large QTY

Looking for guidance replicating a problematic object. Here's what I need to do:

- Replicate the ContentNote object to get my local copy of content I've inserted
- I know there to be about 170k records here
- I need to get the full base64 content in order to perform mass manipulation of the content body itself, so the base64 has been maxed out on purpose.
- I've tried all 3 replicate options but the only one that seems that it could work is the normal "Replicate" without pkchunk.

- pkchunk has an error saying that the it's not allowed for this object
- replicatelarge is also not allowed due to base64 field and that fact that it uses the bulk api

- Normal replication ran for 9 hours and seemed to time out.
- Another run resulted in the errors at the bottom here after 2.5 hrs running.

Thoughts on next steps?

I've not had success initially trying to export with Dataloader as there is not a "ContentNote" object and the contentversion, contentdocument objects don't seem to provide the records I'm looking for.

----

--- Starting SF_Replicate for ContentNote V3.6.5
00:01:44: Parameters: SF_PRODUCTION ContentNote Version: V3.6.5
00:01:44: Drop ContentNote_Previous if it exists.
00:01:44: Create ContentNote_Previous with new structure.
00:01:44: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC
00:01:44: Parameters: replicate ContentNote_Previous pcname\SQLEXPRESS BACKUP_SF_PROD SF_PRODUCTION
02:45:52: Error: System.Reflection.TargetInvocationException: An exception occurred during the operation, making the result invalid. Check InnerException for exception details. ---> System.Net.WebException: The underlying connection was closed: The conne
ction was closed unexpectedly.
at System.Web.Services.Protocols.WebClientAsyncResult.WaitForResponse()
at System.Web.Services.Protocols.WebClientProtocol.EndSend(IAsyncResult asyncResult, Object& internalAsyncState, Stream& responseStream)
at System.Web.Services.Protocols.SoapHttpClientProtocol.InvokeAsyncCallback(IAsyncResult result)
--- End of inner exception stack trace ---
at DBAmpNet2.SFDataLayer3.Read()
at System.Data.SqlClient.SqlBulkCopy.ReadFromRowSourceAsync(CancellationToken cts)
at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at DBAmpNet2.Processor.Run()
02:45:52: 0 rows copied.
02:45:52: DBAmpNet2 Operation FAILED.
02:45:54: Error: Replicate program was unsuccessful.
02:45:54: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe Exportsoap "Replicate" "ContentNote_Previous" "pcname\SQLEXPRESS" "BACKUP_SF_PROD" "SF_PRODUCTION"
--- Ending SF_Replicate. Operation FAILED.
Msg 50000, Level 16, State 1, Procedure SF_REPLICATE, Line 370 [Batch Start Line 43]
--- Ending SF_Replicate. Operation FAILED.
1 person has
this problem
+1
Reply
  • The salesforce api has horrible performance retrieving binary blobs because of 2 reasons:

    1. You have a ratio of 1 call per row (i.e no batching). This means 170K rows takes 170K api calls. This will likely exceed your daily api call count.

    2. The response time per call can be up to 3 to 7 seconds. Assuming 5 seconds, it would take 236 hours or about 10 days to retrieve your documents.

    Therefore, you cannot use sf_replicate. I would recommend setting MaxBase64 back to 0 and run the following query to update the change internally to the linked server: Select * from salesforce...sys_sfobjects

    Then, decide between the following 2 approaches:

    1. If you really need all the notes, use the salesforce weekly export service and request that binary information be included.

    2. If you only need some of the notes, replicate the contentnote table with MaxBase64 set to 0. This will go fast because the binary info is not downloaded. Then create a table of ids that you want to binary blob and use sf_downloadBlobs to download the binary file. SF_DownloadBlobs uses the bulkapi but you still have a ratio of 1:1. Start small with a table of 100 or so ids to get a timing test.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Hi Bill,

    Thanks for the quick reply! This information is gold. I've got some options now and I'll likely go with a plan that looks something like this:

    1. Request the SF Data export (2 great reasons, I'll get other users-inserted ContentNotes with this. And, won't be hit with API daily limit.)

    2. Since the export won't be live, I'll need to be sure that my mass-update is aware of any notes that have been edited since the export(to skip them). I think I'll handle that this way... I've got only access to my ContentNotes so I'll need to insert a ContentDocumentLink for each of the notes I don't automatically get access to. Once those are inserted, my fast 0-base64 replication will give me full up to date LastModifiedDates on all notes. I'll then only mass-update notes that are not edited.

    Do you know if inserting ContentNote with a batch size of 100 is breaking that into 100 api calls? It seems to show a single callout in the logs for that and it runs very fast. I had 3 simultaneous insertions of 10k ContentNotes each. Net outcome was 30k notes inserted in 24 minutes. They are generally short notes from a previous CRM system, under 800 characters.

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

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

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

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

  • I think the answer is that the SF_Bulkops will knock it down to 2:1 but SF_Tableloader will use 100:1.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Using BulkOps, here's inserting 100. Seems like it's 1 callout.

    58 seconds:

    Starting SF_BulkOps for ContentNote_LoadTest V3.6.5
    14:30:35: Run the DBAmp.exe program.
    14:30:35: DBAmp Bulk Operations. V3.6.5 (c) Copyright 2006-2017 forceAmp.com LLC
    14:30:35: Inserting ContentNote_LoadTest (pcname\SQLEXPRESS / BACKUP_SANDBOX_FULLIMPORT).
    14:30:37: DBAmp is using the SQL Native Client.
    14:30:37: Batch size reset to 100 rows per batch.
    14:30:38: SOAP Headers:
    14:30:39: Warning: Column 'SQL_ID' ignored because it does not exist in the ContentNote object.
    14:30:39: Warning: Column 'SQL_KEEP' ignored because it does not exist in the ContentNote object.
    14:30:39: Warning: Column 'SO_CONTACTID' ignored because it does not exist in the ContentNote object.
    14:30:39: Warning: Column 'SF_LINKEDRECORDID' ignored because it does not exist in the ContentNote object.
    14:31:32: 100 rows read from SQL Table.
    14:31:32: 100 rows successfully processed.
    14:31:32: Percent Failed = 0.000.
    --- Ending SF_BulkOps. Operation successful.

    Update:
    20 seconds:

    --- Starting SF_BulkOps for ContentNote_LoadTest V3.6.5
    14:32:34: Run the DBAmp.exe program.
    14:32:34: DBAmp Bulk Operations. V3.6.5 (c) Copyright 2006-2017 forceAmp.com LLC
    14:32:34: Updating Salesforce using ContentNote_LoadTest (pcname\SQLEXPRESS / BACKUP_SANDBOX_FULLIMPORT) .
    14:32:36: DBAmp is using the SQL Native Client.
    14:32:36: Batch size reset to 100 rows per batch.
    14:32:37: SOAP Headers:
    14:32:37: Warning: Column 'SQL_ID' ignored because it does not exist in the ContentNote object.
    14:32:37: Warning: Column 'SQL_KEEP' ignored because it does not exist in the ContentNote object.
    14:32:37: Warning: Column 'SO_CONTACTID' ignored because it does not exist in the ContentNote object.
    14:32:37: Warning: Column 'SF_LINKEDRECORDID' ignored because it does not exist in the ContentNote object.
    14:32:37: Warning: Column 'CreatedDate' ignored because it not updateable in the ContentNote object.
    14:32:37: Warning: Column 'LastModifiedDate' ignored because it not updateable in the ContentNote object.
    14:32:55: 100 rows read from SQL Table.
    14:32:55: 100 rows successfully processed.
    14:32:55: Percent Failed = 0.000.
    --- Ending SF_BulkOps. Operation successful.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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