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

Different result using openquery and sf_replicate

Hi,

I am using DBAmp v3.3.3 to replicate the Account and Task tables.

Example:
exec sf_replicate 'salesforce', 'Account'

But unfortunately, there are records missing in my local database for Account and Tasks. Other tables are fine.

For example, if I run :
select COUNT(ID) from account
select * from openquery(salesforce, 'select COUNT(ID) from account')

I get different results !

I also tried synchronizing the archived and deleted records :
exec sf_replicateIAD 'salesforcesubset', 'Account'

But unfortunately, again, I can't find all the accounts that I am supposed to find.

Thank you in advance for your help
1 person has
this question
+1
Reply
  • Is the SF user on the linked server a sys admin and have the view all data privilege?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Also, how different are the Account counts for when you ran Replicate and counted the number of IDs via openquery?

    Can we also see the complete message output from the replicate command?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • [Q] Is the SF user on the linked server a sys admin and have the view all data privilege?

    [A] If I go to SalesForce > Setup > Profiles, I can see that the remote login user of the linked server is assigned to a profile that can do "View All" on Account and Task.

    [Q] Also, how different are the Account counts for when you ran Replicate and counted the number of IDs via openquery?

    [A] Here are the numbers for Account :
    select COUNT(ID) from account => 2195892
    select * from openquery(salesforce, 'select COUNT(ID) from user') => 2195912

    That's not a lot but enough for our users to report it as a bug :-)
    And it is not the recent data that is missing, because an Account created several months ago is not present.

    For Task, I can't tell the numbers because I have a timeout running it from openquery or the SF developer console. But there seem to be a lot of tasks missing.

    [Q] Can we also see the complete message output from the replicate command?

    [A] Sure (here is the log of the SQL job step)

    --- Starting SF_Replicate for Account V3.3.3 [SQLSTATE 01000] (Message 0)
    05:22:03: Parameters: SalesForce Account pkchunk Version: V3.3.3 [SQLSTATE 01000] (Message 0)
    05:22:03: Drop Account_Previous if it exists. [SQLSTATE 01000] (Message 0)
    05:22:03: Create Account_Previous with new structure. [SQLSTATE 01000] (Message 0)
    05:22:03: Run the DBAmp.exe program. [SQLSTATE 01000] (Message 0)
    05:22:03: DBAmp Bulk Operations. V3.3.3 (c) Copyright 2006-2016 forceAmp.com LLC [SQLSTATE 01000] (Message 0)
    05:22:03: Populating local table Account_Previous , XXXXXXXXXXX / YYYYYYYYYYYY . [SQLSTATE 01000] (Message 0)
    05:22:04: DBAmp is using the SQL Native Client. [SQLSTATE 01000] (Message 0)
    05:22:04: Opening SQL Server rowset [SQLSTATE 01000] (Message 0)
    05:22:04: Using the bulkapi with polling every 60 seconds. [SQLSTATE 01000] (Message 0)
    05:22:05: Job still running. [SQLSTATE 01000] (Message 0)
    05:22:20: Job still running. [SQLSTATE 01000] (Message 0)
    05:23:20: Job still running. [SQLSTATE 01000] (Message 0)
    05:24:20: Job still running. [SQLSTATE 01000] (Message 0)
    05:25:21: Job still running. [SQLSTATE 01000] (Message 0)
    05:26:21: Job still running. [SQLSTATE 01000] (Message 0)
    05:27:21: Job still running. [SQLSTATE 01000] (Message 0)
    05:28:21: Job still running. [SQLSTATE 01000] (Message 0)
    05:29:22: Job still running. [SQLSTATE 01000] (Message 0)
    05:30:22: Job still running. [SQLSTATE 01000] (Message 0)
    05:31:22: Job still running. [SQLSTATE 01000] (Message 0)
    05:32:22: Job still running. [SQLSTATE 01000] (Message 0)
    05:33:23: Job still running. [SQLSTATE 01000] (Message 0)
    05:34:23: Job still running. [SQLSTATE 01000] (Message 0)
    05:35:23: Job still running. [SQLSTATE 01000] (Message 0)
    05:36:23: Job still running. [SQLSTATE 01000] (Message 0)
    05:37:24: Job still running. [SQLSTATE 01000] (Message 0)
    05:38:24: Job still running. [SQLSTATE 01000] (Message 0)
    05:39:24: Job still running. [SQLSTATE 01000] (Message 0)
    05:40:24: Job still running. [SQLSTATE 01000] (Message 0)
    05:41:25: Job still running. [SQLSTATE 01000] (Message 0)
    06:23:08: 837000 rows downloaded. [SQLSTATE 01000] (Message 0)
    07:15:00: 1583000 rows downloaded. [SQLSTATE 01000] (Message 0)
    07:49:48: 2195892 rows downloaded. [SQLSTATE 01000] (Message 0)
    07:49:48: 2195892 rows copied. [SQLSTATE 01000] (Message 0)
    07:49:49: Drop Account if it exists. [SQLSTATE 01000] (Message 0)
    07:49:49: Rename previous table from Account_Previous to Account [SQLSTATE 01000] (Message 0)
    Caution: Changing any part of an object name could break scripts and stored procedures. [SQLSTATE 01000] (Message 15477)
    07:49:49: Create primary key on Account [SQLSTATE 01000] (Message 0)
    --- Ending SF_Replicate. Operation successful. [SQLSTATE 01000] (Message 0). The step succeeded.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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

  • Can you send some times that you are available for a web meeting today to support at forceamp.com
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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