ss_Replica is a Stored Procedure you execute within your enabled database.
Prerequisites
A working Environment and the SQL Sales Daemon is running (see Environment Setup)
SQL Server
Compiled stored procedure ss_Replica (see DatabaseEnabling)
ss_Log replication log
Replication is logged in table “ss_Log” which is automatically created and maintained for all ss_Replica runs and the same for ss_Delta runs (see next section).
Note, the table is created within the schema to which you are running ss_Replica, hence if you ran with uat.ss_Replica, the resultant entry for the given object will be to table uat.ss_Log.
Field | Purpose |
ReplicaName | The Salesforce object name or a Custom table name if one has been specified, created as a replica table in the given enabled Database |
ObjectName | Source Salesforce Object of this replication log (will be the same as ReplicaName if no customisation has occurred with the “Table:” switch |
CustomReplica | Indicates if the Replica is Custom (i.e. not a basic replication of a Salesforce Object Name but ReplicaName is a custom table name |
TableCreatedDate | Serves no functional purpose, is for information purposes only |
MaxSystemDate | This is how the ss_Delta process determines how to delta replicate |
LogDate | Last log datetime |
TopRow | Captures any Top commands, for example 'Account:Top100' |
Subset | Captures any Column Subset commands, for example 'Subset(Name,BillingCountry)' |
Status | Possible values:
FAILURE (indicates a failure has occurred, see Detail field) FULL (indicates a Full replication occurred) DELTA (indicates a partial replication occurred, via ss_Delta) |
WhereInput | Captures any Where clause commands injected into the replication |
Detail | Success or Failure detail |
ss_Log_Working detailed replication log
For more granular detail of every replication run, each step is logged in “ss_Log_Working” which can be helpful in monitoring longer running processes of millions of rows, as the BULK connection method will deliver the data payload in chunks, which are logged and so can be monitored to check progress.
Field | Purpose |
Environment | The specified Salesforce Environment, defined in the SQL Sales Configuration tool |
Task | “ss_Replica” |
ObjectName | Source Salesforce Object of this replication log (will be the same as ReplicaName if no customisation has occurred with the “Table:” switch |
PreProcessPoint | The process which is about to be started |
Detail1 | The Salesforce object name or a Custom table name if one has been specified, created as a replica table in the given enabled Database |
Detail2 | The contents of parameter @Special1 |
Detail3 | The contents of parameter @Special2 |
Detail4 | Logs the current Chunk, if a Salesforce Id, this is the latest Id the process has got to |
Parameters
Parameter | Purpose |
@Env | SQL Sales Environment Name |
@ObjectName | Salesforce Object Name (for example Account)
Can be accompanied with :TopXXX where XXX is a number exec ss_Replica 'DEMO', 'Account:Top10’
Can have an “_All” suffix to return soft deleted and/or archived data exec ss_Replica 'DEMO', 'Account_All' |
@Special1 | Optional, in simple usage, null
Subset(field1,field2,field3) – where field1 etc are Salesforce api fieldnames for the given object. For example in the case of the object being Account, this could be: Subset(Name,Phone,BillingCity)
Table:<custom table name> - for example 'Table:OppTest' No special delimiter is required between Subset(x,y,z) and Table:xxx, for example leave nothing or a space: ‘Subset(Name,Phone,BillingCity) Table:AccountTest'
LinkedEntityId Only relevant if you are replicating Object ‘ContentDocumentLink’ see the relevant detail in this section, for example Exec ss_Replica ‘demo’, ‘ContentDocumentLink’,’LinkedEntityId’,’a,b,c,d’ (where ‘a,b,c,d’ is a comma delimited list of up to 200 Entity Ids for example Account Ids).
ContentDocumentId Only relevant if you are replicating Object ‘ContentDocumentLink’ see the relevant detail in this section, for example Exec ss_Replica ‘demo’, ‘ContentDocumentLink’,’ContentDocumentId’,’a,b,c,d’ (where ‘a,b,c,d’ is a comma delimited list of up to 200 ContentDocument Ids)
|
@Special2 | Optional, in simple usage, null
Where <SOQL> - for example ‘Where StageName = ‘’Closed Won’’’
If @Special1 = LinkedEntityId Provide a comma delimited string of up to 200 Entity Id (for example 200 Account Ids)
If @Special1 = ContentDocumentId Provide a comma delimited string of up to 200 ContentDocumentIds |
Simple Example
exec ss_Replica 'DEMO', 'Account'
Specifying Batchsize
By default ss_Replica will use a batchsize of 2000 (the maximum possible via the Salesforce SOAP api). Should you wish to use a smaller batchsize, specify this by passing in the custom batchsize when passing in the Objectname as below:
exec ss_Replica 'DEMO', 'Account(25)'
Retrieving soft deleted & archived data (Query All)
By default ss_Replica will automatically exclude IsDeleted = 1/True records as well as isArchived = 1/True. To include these records, append “_All” to your specified Objectname as below, note ss_Delta does not support the “_All” switch as it is not possible to reliably validate if a local IsDeleted = 1/True has been auto-purged from the Salesforce recyclebin.
exec ss_Replica 'DEMO', 'Account_All'
Quick Top check
This doesn’t replicate back to the given object table name as it by default outputs as a resultset to the management studio results pane, to facilitate quick checks of data and metadata. However the same data is also present in a <ObjectName> + _Check table, for example Account_Check. Note (see following documentation) – combining Top with a custom replica table will result in the Top number of output rows being written to the specified SQL replica table as opposed to it being an instruction for the output to the screen only. Output to screen:
exec ss_Replica 'DEMO', 'Account:Top5'
Output to the specified custom replica table:
exec ss_Replica 'DEMO', 'Account:Top5',’Table:AccountTopTest’
Subset Example
exec ss_Replica 'DEMO', 'Account','Subset(Name,BillingCountry)'
Valid fields (i.e. that exist on the specified object and for which you have permission to query, will only be returned, although note some basic fields to support the replication logic to function correctly will be imposed (for example Id, isDeleted; SystemModStamp).
Note, for larger sets of subset fields, you may prefer to input in a list form, example shown below, which is an acceptable input method to the @Special1 input parameter:
exec ss_Replica 'DEMO', 'Opportunity'
,'Subset(
AccountId
,Amount
,CampaignId
,CloseDate
,Name
,NextStep
,OrderNumber__c
,OwnerId
,Pricebook2Id
,Probability
,StageName
,SystemModstamp
,TotalOpportunityQuantity
,TrackingNumber__c
,Type)'
Or
exec ss_Replica 'DEMO', 'Opportunity'
,'Subset(AccountId,Amount,CampaignId,CloseDate,Name,NextStep,OrderNumber__c,OwnerId,Pricebook2Id,Probability,StageName,SystemModstamp,TotalOpportunityQuantity,TrackingNumber__c,Type)'
Combined Subset, Batchsize and Top check example
exec ss_Replica 'DEMO', 'Account(25):Top100','Subset(Name,BillingCountry)'
Custom Replica Example
exec ss_Replica 'DEMO', 'Opportunity','Table:OppTest'
In this example, Salesforce Opportunity data will be replicated to the “OppTest” SQL table. This can be maintained with ss_Delta if required, entirely independently of any potential “Opportunity” replica
exec ss_Delta 'DEMO', 'Opportunity','Table:OppTest'
existing in the same deployed schema in your given Database.
For example running
exec ss_Replica 'DEMO', 'Opportunity','Table:OppTest'
exec ss_Replica 'DEMO', 'Opportunity'
will define two separate replica tables of Salesforce Opportunity data, one called Opportunity, the other called OppTest, which can both be maintained via ss_Delta, provided the “Table:” switch is applied consistently:
exec ss_Delta 'DEMO', 'Opportunity','Table:OppTest'
exec ss_Delta 'DEMO', 'Opportunity'
Combining Top with Custom Replica
If Top is combined with a custom replica instruction, the limit of rows is applied to the custom created replica object and therefore there is no _check table output to the SSMS results pane.
Where Clause Example
exec ss_Replica 'DEMO', 'Opportunity','Table:OppTest','Where StageName = ''Closed Won'''
In this example, a where clause has been added, which will be applied to the replica and can be applied to ss_Delta. It is entirely optional to apply this to a custom table or against the full SF object name
exec ss_Delta 'DEMO', 'Opportunity','Table:OppTest','Where StageName = ''Closed Won'''
Note, any custom Replica created with a Where clause must continue to have that same identical Where clause applied any subsequent ss_Delta calls, otherwise the Delta logic will be compromised as there will be no consistency between the scope of the original ss_Replica and the delta data retrievals via ss_Delta.
In this example, a where clause has been added to the base Salesforce object replica (i.e. no custom table usage), the clause Type = New Customer has been passed in
exec ss_Replica 'DEMO', 'Opportunity',null,'Where Type = ''New Customer'''
exec ss_Delta 'DEMO', 'Opportunity',null,'Where Type = ''New Customer'''
Combining Top with Custom Replica and where clause
exec ss_Replica 'DEMO', 'Opportunity:Top20','Table:OppTest','Where StageName = ''Closed Won'''
Combining Top with Field Subset, Custom Replica and where clause
exec ss_Replica 'DEMO', 'Opportunity:Top20','Subset(Name,Type,StageName) Table:OppTest','Where StageName = ''Closed Won'''
ContentDocumentLink
The Salesforce Content Model object acts as a junction between a Document and the object (for example Opportunity) that has the Document linked to it. It allows multiple different objects to be linked to the same Document.
Field | Purpose |
LinkedEntityId | Object Id (for example Account.Id; Opportunity.Id) etc |
ContentDocumentId | Document Id |
The Salesforce api limits the retrieving of ContentDocumentLink records to either a maximum of 200 LinkedEntityId or 200 ContentDocumentId. For this reason replicating ContentDocumentLink can be challenging, however SQL-Sales is able to fully return all available records en masse using the conventional ss_Replica command:
exec ss_Replica 'DEMO', 'ContentDocumentLink'
if specific subsets of either LinkedEntityId or ContentDocumentId are required (in batches of 200),
ss_Replica allows you to pass in a comma separated string of up to 200 Salesforce 18 character Ids into optional parameter @Special2. @Special1 is how you indicate if you are passing in LinkedEntityId Ids or ContentDocumentId Ids. Note this is supported purely as a helper shortcut to retrieve via the Salesforce api limits on querying ContentDocumentLink.
Example of usage (LinkedEntityId)
@Special1 in this example is 'LinkedEntityId'
@Special2 in this example is the variable [@Ids] which has the comma separated Ids for example up to 200 Opportunity Ids
exec ss_Replica 'DEMO', 'ContentDocumentLink','LinkedEntityId',@Ids
As a helper, below are three suggested methods by which you can create a comma separated string for which can be passed into the variable @Ids
Note, in the below examples of 200 Account Ids, Account has been prior replicated
Option1 (COALESCE)
DECLARE @Ids NVARCHAR(4000)
SELECT @Ids = COALESCE(@Ids + ',', '') + CAST(Id AS VARCHAR)
FROM (SELECT TOP 200 Id FROM Account) AS Top200
exec ss_Replica 'DEMO', 'ContentDocumentLink','LinkedEntityId',@Ids
Option2 (STUFF | XML PATH)
DECLARE @Ids NVARCHAR(4000)
SELECT @Ids = ( STUFF((SELECT TOP 200 ',' + CAST(Id AS VARCHAR)
FROM Account ORDER BY Id FOR XML PATH('')), 1, 1, ''))
exec ss_Replica 'DEMO', 'ContentDocumentLink','LinkedEntityId',@Ids
Option3 (STRING_AGG)
DECLARE @Ids NVARCHAR(4000)
SELECT @Ids = ( SELECT STRING_AGG(CAST(Id AS VARCHAR), ',') WITHIN GROUP (ORDER BY Id) AS CommaSeparatedIds
FROM (SELECT TOP 200 Id FROM Account ORDER BY Id) AS Top200)
exec ss_Replica 'DEMO', 'ContentDocumentLink','LinkedEntityId',@Ids
Example of usage (ContentDocumentId)
@Special1 in this example is 'ContentDocumentId'
@Special2 in this example is the variable [@Ids] which has the comma separated Ids for example up to 200 Opportunity Ids
exec ss_Replica 'DEMO', 'ContentDocumentLink','ContentDocumentId',@Ids
As a helper, below are three suggested methods by which you can create a comma separated string for which can be passed into the variable @Ids
Note, in the below examples of 200 Document Ids, ContentVersion has been prior replicated
Option1 (COALESCE)
DECLARE @Ids NVARCHAR(4000)
SELECT @Ids = COALESCE(@Ids + ',', '') + CAST(ContentDocumentId AS NCHAR(18))
FROM (SELECT DISTINCT TOP 200 ContentDocumentId FROM ContentVersion) AS Top200
exec ss_Replica 'DEMO', 'ContentDocumentLink','ContentDocumentId',@Ids
Option2 (STUFF | XML PATH)
DECLARE @Ids NVARCHAR(4000)
SELECT @Ids = ( STUFF((SELECT TOP 200 ',' + CAST(ContentDocumentId AS NCHAR(18))
FROM ContentVersion ORDER BY ContentDocumentId FOR XML PATH('')), 1, 1, ''))
exec ss_Replica 'DEMO', 'ContentDocumentLink','ContentDocumentId',@Ids
Option3 (STRING_AGG)
DECLARE @Ids NVARCHAR(4000)
SELECT @Ids = ( SELECT STRING_AGG(CAST(ContentDocumentId AS NCHAR(18)), ',') WITHIN GROUP (ORDER BY ContentDocumentId) AS CommaSeparatedIds
FROM (SELECT TOP 200 ContentDocumentId FROM ContentVersion ORDER BY ContentDocumentId) AS Top200)
exec ss_Replica 'DEMO', 'ContentDocumentLink','ContentDocumentId',@Ids