Using Column Subset views


Using Column Subset views


Problem

Objects in Salesforce that contain over 325 columns may produce an error when either replicated or refreshed. The error occurs because the maximum limit of the SELECT query statement in the Salesforce API is 10,000 characters. A large number of columns in an object produce a SELECT query larger than 10,000 characters.

Solution

The solution is to take advantage of Column Subset views. These views represent a user specified subset of the columns designed to fit within the 10,000 character limit.

By attaching a suffix to the table name, DBAmp includes only those columns with names that fall within the alphabetic range. For example, the following SQL statement returns all columns with names beginning with any letter between A-M inclusive:

SELECT * FROM SALESFORCE_LS.Cdata.Salesforce.Account_ColumnSubsetAM

The suffix must have the following format: the string “_ColumnSubset” and two single letters indicating the alphabetic range.

The following columns are always returned:

  • ID
  • SystemModstamp
  • LastModifiedDate
  • CreatedDate

Retrieving a Full Copy

To retrieve a full copy of the object data, use two or more column subset views. For example, to replicate a large Account using column subset views, use the following command:

EXEC SF_replicate 'SALESFORCE','Account_ColumnSubsetAM'
EXEC SF_replicate 'SALESFORCE','Account_ColumnSubsetNZ'

You can use ColumnSubset Views in SELECT statements (but not OPENQUERY) and in the SF_Replicate and SF_Refresh stored procedures.