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.