Replicating fails for my big tables

  • 30 March 2023
  • 1 reply

Userlevel 4

When I run SF_Replicate on my tables with lots of fields, like Account and Case, it fails. SF_Mirror also fails, and I can’t run SF_Refresh because there’s been a field added so it just tries to run SF_Replicate anyway. The message output looks like this:

--- Starting SF_Replicate for Account V5.1.8
12:58:41: Parameters: Salesforce Account  Version: V5.1.8
12:58:41: DBAmpAZ Copyright c 2021 CData Software, Inc.
12:58:41: Using options specified in Options parameter: soap
12:58:42: Using settings from Registry.
12:58:42: Using FullCopy to replicate the Account table.
12:58:42: Server: DL-7KVJRL3, Database: olddbamp, Linked Server: Salesforce
12:58:42: Using the Salesforce SOAP API.
12:58:43: Opening SQL Server rowset
12:58:43: Error: Commit failed.
Source: Microsoft SQL Server Native Client 11.0
Description: The statement has been terminated.
Source: Microsoft SQL Server Native Client 11.0
Description: Cannot create a row of size 8107 which is greater than the allowable maximum row size of 8060.
12:58:43: Error: DBAmp.exe was unsuccessful.
12:58:43: Error: Command string is C:\"Program Files"\DBAmp\DBAmpAZ.exe FullCopy "Account"  "SQLSERVER"  "SALESFORCE"  "Salesforce backups"  "soap"
--- Ending SF_Replicate. Operation FAILED.
Error: SF_Replicate failed for table Account


Best answer by Dani Moran 30 March 2023, 19:12

View original

1 reply

Userlevel 4

That error is because of the SQL Server restriction on how wide a table can be - all those fields have exceeded the maximum width allowed. There are a few of ways to get around it depending on your version.

1) If there are some fields that you don't need to copy locally, you can remove your integration users' permissions to those fields, which will shorten the row length. In that message output, it doesn't look like it's very much over (8107  versus the maximum of 8060), so it shouldn't require much trimming.

2) SF_Replicate and SF_Mirror have ColumnSubset options that will bring down alphabetic subsets of the columns, which of course serves to shorten the rows in each piece. That would look like: EXEC SF_Replicate '<your linked server>','Account_ColumnSubsetAM' and EXEC SF_Replicate '<your linked server>','Account_ColumnSubsetNZ' to get all data down.

3) Similar to #1, but without needing to go to Salesforce to change permissions, you can use SF_BulkSOQL to bring down data based on a particular SOQL query you designed that only queries those fields that you need local copies of. You can find documentation here for v22:  or in the “DBAmp Stored Procedure Reference” Chapter of your PDF documentation in older versions of DBAmp (<v5.1.9) if your version of DBAmp includes that stored procedure.