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

Limit on data count for SF_Refresh with Bulk API and solution for Hard Delete in Saleforce

Q1. I use SF_Refresh procedure with bulk API and it gets failed everytime when there are huge no rows to be pulled from Salesforce to SQL server ( rows around 500,00 having 250 fields with 2-3 text columns rest are nvarchar)? Is there any special privileges needed on the user account use to refresh or any other alternative to pull huge no of records through SF_Refresh with bulk api?
I don't want to use SF_Replicate as object consists of data around 40 million.
Q2. If we do huge no of hard delete at Salesforce in an object and we manually delete the same no of records from the same object at SQL Server end also. I do this activity thinking that DBAmp won't be needed to perform huge deletion at SQL server for those records deleted at Salesforce. But still when I refresh through sf_refresh with BULK API, it tries to pull data from "
_deleted" after calculating the no of updated/ inserted records and get failed with query time out error. Is there any solution for this , if records has been hard deleted already from both end then how DBAmp tries to find deleted no of records? I don't want to use SF_Replicate as it will take hours to replicate 40 million data for many objects that cant be possible on one day.

In above both case there is no problem with no of batches , those are always sufficient.

Thanks in Advance.
Rahul
1 person has
this question
+1
Reply
  • Can you post the complete message output from the failed refresh with built API?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Hi Bill,

    This is the error for Q2:
    Msg 0, Sev 16, State 1, Line 47 : --- Starting SF_Refresh for ABI_SFA_Activity__c V3.3.2 [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 99 : 23:06:20: Using Schema Error Action of subset [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 269 : 23:06:21: Using last run time of 2017-06-23 23:21:00 [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 323 : 23:06:21: Run the DBAmp.exe program. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:21: DBAmp Bulk Operations. V3.3.4 (c) Copyright 2006-2016 forceAmp.com LLC [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:21: Populating local table ABI_SFA_Activity__c_Delta2017-06-25T23:06:20.880 , WEGDADB99 / BNL_SF . [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:22: DBAmp is using the SQL Native Client. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:22: Opening SQL Server rowset [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:22: Using the bulkapi with polling every 60 seconds. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:23: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:06:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:07:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:08:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:09:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:10:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:11:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:12:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:13:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:14:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:15:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:16:38: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:17:39: Job still running. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 351 : 23:18:39: Job Complete. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 398 : 23:19:17: Identified 2405 updated/inserted rows. [SQLSTATE 01000]
    Msg 7412, Sev 16, State 1, Line 1 : OLE DB provider "DBAmp.DBAmp" for linked server "SF_BNL" returned message "Error 1 : QUERY_TIMEOUT: Your query request was running for too long.". [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 633 : 23:29:18: Error occurred fetching deleted rows. [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 636 : 23:29:18: Error: Cannot execute the query "Select Id from ABI_SFA_Activity__c_Deleted where startdate='2017-06-23 23:21:00'" against OLE DB provider "DBAmp.DBAmp" for linked server "SF_BNL". [SQLSTATE 01000]
    Msg 0, Sev 16, State 1, Line 840 : --- Ending SF_Refresh. Operation FAILED. [SQLSTATE 01000]

    For Q1 : I don't have exact error report for now, but it is something like Fttp connection error as huge no of records get updated
    (around 500,000)
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • So it appears that your sf_refresh timed out on the salesforce server while trying to fetch the deleted rows. The salesforce server will kill any query that runs longer than 2 minutes. One cause of the query running longer on salesforce could be that you are using a sf userid that is not the system admin. This forces salesforce to evaluate the security model for each row. Can you verify whether the sf user on the Security page of the linked server has the System Admin profile ?

    For question 2 above I have the following comments:

    - The latest version of DBAmp contains a sf_replicate option that runs 5 - 7 times faster. I would recommend upgrading to the latest version and using the pkchun option.

    - SF_Refresh always assumes that you are NOT deleting rows locally and that it is his responsibility. You can make a modified version of SF_Refresh for your own needs that skips over the call to get the deleted rows from salesforce.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Thanks a lot Bill for your help ,

    I got your solution for hard deletion case.

    But to avoid the SF_refresh from getting failed for 500,000 rows i am supposed to use user id which has system admin profile on saleforce as on SQL server the linked server is created by system admin already? right?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Our recommendation is that you always use a salesforce user with profile of System Admin. Otherwise, DBAmp has an incomplete view of the data and is subject to security calculations on the salesforce server.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

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

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

  • I have a question regarding the admin account limitations. Our account has the System Administrator profile, but has also had the API Only permission set added. Is this an issue and could we be suffering the additional security calculations?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • As long as the sf user has the "View All Data" and "Modify All data" privilege on salesforce, then salesforce will NOT calculate row by row security.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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