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.