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

SF_ReplicateHistory Error - Cannot get the data of the row from the OLE DB provider "DBAmp.DBAmp"

Hello,

Our DBAmp process works great, we use SF_Replicate and SF_Refresh successfully and regularly for many SFDC objects. However, there are two large history tables I need to replicate. They are: AccountHistory and LeadHistory. I've tried several times now to use SF_ReplicateHistory for this, but the process never succeeds. It runs for many hours and eventually fails. My latest attempt ran for 1 day, 16 hours and 51 minutes, finally failing with the following error message. Any suggestions or ideas for how to replicate these large history tables from SFDC? Thank you in advance for your help.

Command:

exec SF_ReplicateHistory SALESFORCE, 'AccountHistory'

Messages:

--- Starting SF_ReplicateHistory for AccountHistory V3.6.5
13:09:42: Drop AccountHistory_Previous if it exists.
13:09:42: Create AccountHistory_Previous with new structure.
OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error: An error has occurred while fetching rows from salesforce.com: ".
06:01:00: Error occurred populating the _Previous table.
06:01:00: Error: Cannot get the data of the row from the OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
--- Ending SF_ReplicateHistory. Operation FAILED.
Msg 50000, Level 16, State 1, Procedure SF_ReplicateHistory, Line 250 [Batch Start Line 0]
--- Ending SF_ReplicateHistory. Operation FAILED.
1 person has
this problem
+1
Reply
  • Don't use SF_ReplicateHistory. For large tables, anything over 250,000 rows or so, use SF_Replicate with the pkchunk option:

    exec SF_Replicate 'SALESFORCE', 'AccountHistory', 'pkchunk,batchsize(50000)'

    If Salesforce does not support a particular object for pkchunk, just use the bulkapi option:

    exec SF_Replicate 'SALESFORCE', 'AccountHistory', 'bulkapi'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Thank you Justin, this is great information. I was following the DBAmp documentation, and using SF_ReplicateHistory for the 'AccountHistory' object was the actual example in the documentation. I have used pkchunk in the past for other Salesforce objects, which are not as large as our AccountHistory object, but I will try it as you recommended for our AccountHistory, and if needed I will also try the bulkapi option. I will report my results to this post.

    Also, will you please let me know when may be the right time to use SF_ReplicateHistory? Is it appropriate for history tables that are less than 250,000 rows?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • I would just abandon using SF_ReplicateHistory and use SF_Replicate with pkchunk or bulkapi options moving forward.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Thank you Justin. I will report back with my results.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Great news! That worked! 48 minutes 12 seconds. Problem solved. Thank you Justin!

    Command:

    exec SF_Replicate SALESFORCE, 'AccountHistory', 'pkchunk,batchsize(50000)'

    Messages: (I abbreviated for this post ..^..^..^..^..^..^..^..^..^..^..^..^..)

    --- Starting SF_Replicate for AccountHistory V3.6.5
    09:46:01: Parameters: SALESFORCE AccountHistory pkchunk,batchsize(50000) Version: V3.6.5
    09:46:01: Drop AccountHistory_Previous if it exists.
    09:46:01: Create AccountHistory_Previous with new structure.
    09:46:02: DBAmpNet2 3.6.5.0 (c) Copyright 2015-2017 forceAmp.com LLC
    09:46:02: Batch size reset to 50000 rows per batch.
    09:46:02: Parameters: replicate AccountHistory_Previous <> SALESFORCE pkchunk,batchsize(50000)
    09:46:04: Using Parent parameter:Account
    09:46:05: Job 7506O000999eHIlPZM created on salesforce.
    09:46:05: Using the bulkapi with polling every 60 seconds
    09:46:06: Job still running.
    09:46:22: Job still running.
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:13:42: Job still running.
    10:14:44: Job still running.
    10:15:45: Job Complete.
    10:23:07: File downloaded successfully: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8mJQAT-7537O000993t6Im.csv
    10:23:07: File downloaded successfully: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8mMQAT-7537O000993t6EQ.csv
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:23:07: File downloaded successfully: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8npQAD-7537O000993t6N3.csv
    10:23:07: Files downloaded complete.
    10:23:10: Loading file into SQL Server: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8maQAD-7537O000993t6F4.csv
    10:23:10: Loading file into SQL Server: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8mbQAD-7537O000993t6HK.csv
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:23:13: 313133 rows loaded. - D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8mbQAD-7537O000993t6HK.csv
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:32:19: Loading file into SQL Server: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8nYQAT-7537O000993t6OQ.csv
    10:32:25: 4700050 rows loaded. - D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8nUQAT-7537O000993t6Gm.csv
    10:32:25: Loading file into SQL Server: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8nZQAT-7537O000993t6MZ.csv
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:32:59: 5163289 rows loaded. - D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8nZQAT-7537O000993t6MZ.csv
    10:32:59: File loaded successfully: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8mbQAD-7537O000993t6HK.csv
    10:32:59: File loaded successfully: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8meQAD-7537O000993t6Ep.csv
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:32:59: File loaded successfully: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8nZQAT-7537O000993t6MZ.csv
    10:32:59: File load complete.
    10:32:59: 280925210 rows copied.
    10:32:59: Deleted file: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8maQAD-7537O000993t6F4.csv
    10:32:59: Deleted file: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8mbQAD-7537O000993t6HK.csv
    ..^..^..^..^..^..^..^..^..^..^..^..^..
    10:33:01: Deleted file: D:\tmp\DBAmp Work Directory\7506O000999eHIlPZM-7541O00099JR8nZQAT-7537O000993t6MZ.csv
    10:33:01: DBAmpNet2 Operation successful.
    10:33:03: Drop AccountHistory if it exists.
    10:33:03: Rename previous table from AccountHistory_Previous to AccountHistory
    Caution: Changing any part of an object name could break scripts and stored procedures.
    10:33:03: Create primary key on AccountHistory
    --- Ending SF_Replicate. Operation successful.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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