Prioritize DBamp SF_MirrorAll Refresh Sequence
The importance of function DBamp SF_MirrorAll in the integration of SQL server and Salesforce is remarkable. It lets the user access Salesforce objects via SQL , along with DML OPERATIONS as per the dynamic demand.
However, there are instances where finding solutions becomes quite tricky especially if it is for a developer with a little or no prior experience.
This article emphasizes modifying the standard stored procedure of DBAmp, i.e. SF_MirrorAll to fulfil the client demand of prioritizing replicate/refresh via. SF MirrorAll on the basis of Salesforce Object Data count.
By default it replicate/refresh in alphabetical order. However, the client seeks it to prioritize its functionality in chronology from highest data count to lowest data count.
Modifying DBamp SF_MirrorAll StoredProcedure to Prioritize Replicate/Refresh of Salesforce Object on the basis of Object data Count
Arranging objects chronologically in a descending order can be quite challenging if you are not well versed with the SF_Mirror and SF_MirrorAll functionality in DBAmp.
Taking the reference of code as stated below will help you to meet your objective without much hassle.
To get a better understanding of the code, we have divided it into 4 steps as follows-
Step1: Calculating data count of each Salesforce Object
In [SF_MirrorAll] Stored Procedure, we first need to calculate the data count.
To make the job easy for you, we recommend to use the code that is mentioned below-
Drop table if exists #tmpSF; (Create Table #tmpSF ([Name] sysname not null, Queryable varchar(5) not null)
declare @sql nvarchar(4000)
set @sql = ‘Select Name,Queryable from ‘ + @table_server + ‘…sys_sfobjects’
Insert #tmpSF EXEC (@sql)
if (@@error <> 0) goto ERR_HANDLER
Here, the code ‘#tmpSF’ includes the objects by SF_mirrorAll; To identify the temporary table that consists all the elements of salesforce.
Step2: Create another temp table named #tmpSF_new
Drop the existing table and create a new one. Then, all the objects in the local database where the last SF_Mirror executed, we need to find the row count of it. And calculate the row count of all the salesforce tables placed locally (this finds the row count hence mandatory).
Drop table if exists #tmpSF_New;
;with cte as (
SELECT OBJECT_NAME(id) AS TableName,
rowcnt AS [RowCount]
FROM sysindexes s
INNER JOIN sys.tables t
ON s.id = t.OBJECT_ID
WHERE s.indid IN ( 0, 1, 255 )
AND is_ms_shipped = 0
)
Step3: Transferring data to #tmpSF_new in descending order based on data count
Now place all the data from #tmpSF into new created table i.e. #tmpSF_new as per the row count. This is to achieve an order so that the tables can be placed in tmpSF_new in descending order based on data count
select * into #tmpSF_New
from #tmpSF
left join cte on #tmpSF.name=cte.TableName
where #tmpSF.Queryable=1
and #tmpSF.name not in (select tablename from DBAmpTableOptions where SkipTable=1 )
order by cte.[RowCount] desc;
if (@@error <> 0) goto ERR_HANDLER
Step4: Now ensure that DBamp SF_MirrorAll retrieve data from new #tmpSF_New
Make required changes where SF MirrorAll’s cursor is reading data from old #tmpSF to #tmpSF_New by using following code-
declare tbls_cursor cursor local fast_forward
for select [Name],Queryable
from #tmpSF_New
order by #tmpSF_New.[RowCount] desc;
Conclusion
Now SF MIRROR WILL replicate/refresh in chronology from high data count to low data count. Contact us to find more such solutions to the typical Salesforce challenges and possibilities.
