Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Accept

Popular articles

SQList version history

Download a previous version of SQList

How do I install or upgrade AxioWorks SQList?

Configure authentication methods to SharePoint

Security Considerations for Installing SQList

More support options:

Use the search page;

Email us at support@axioworks.com;

Send us a message using the Contact Us page.

Force SQList to re-sync a SQL table at a scheduled time


SQList does not have a feature to force the re-sync of SQL tables, but this can be easily achieved using a scheduled task and the sqlcmd utility.

Important: All these steps have be performed on a machine that has SQL server installed and can connect to SQL database updated by SQList; preferably it should be the destination SQL Server used by SQList.

First of all, ensure that you have the sqlcmd utility (which comes with SQL Server) by opening a command prompt and type:


sqlcmd /?


You should see an output like this:




1. Identify the tables you want to re-sync


To do that, run the following SQL on the destination SQL database and look in the "SaveAsTableName" column for the names of the tables you want to re-sync:



select * from [(SQList.ReplicatedLists)]






2. Create a stored procedure on the destination SQL database



Create a new stored procedure in the destination SQL database that contains the SQL to force the re-sync.

This example forces the re-sync of a table named "PortalPublic_Products":


create procedure dbo.ForceResync as

update [(SQList.ReplicatedLists)] set [LastProcessedChangeLogID] = null where [SaveAsTableName] = 'PortalPublic_Products';






To re-sync individual tables, use this SQL:

update [(SQList.ReplicatedLists)] set [LastProcessedChangeLogID] = null where [SaveAsTableName] = 'you_table_name_1';
update [(SQList.ReplicatedLists)] set [LastProcessedChangeLogID] = null where [SaveAsTableName] = 'you_table_name_2';

-- add as many lists as needed



To re-sync all tables belonging to a SharePoint site, use this SQL:

declare @webURL as varchar(500) = 'https://your_domain.sharepoint.com/sites/your_site' 

delete from [(SQList.ChangeLog)] where [WebGUID] = (select top 1 [WebGUID] from [(SQList.ReplicatedLists)] where [WebURL] = @webURL);
update [(SQList.ReplicatedLists)] set [LastProcessedChangeLogID] = null where [WebURL] = @webURL;



3. Create a Windows scheduled task that runs the stored procedure


To create a task the Windows Task Scheduler, launch the Task Scheduler and follow these steps:


Right-click and create a New Folder named "AxioWorks" (or any name you like):






Select the new folder, right-click and Create a Basic Task:





Give the new task a Name and, optionally, a Description:





Click Next, select a Daily trigger, and enter the time of day you want the task to run (that is the time you want to force the re-sync):




Next, select the option to Start a program:




In the Program/Script field enter:


sqlcmd


In the Add Arguments (optional) field enter:


-S <you_sql_server_instance> -U <sql_username> -P <password> -Q "exec <database_name>.dbo.ForceResync;"

Ensure you enter the name of the destination database as <database_name>.
The username and password are for SQL credentials only. To use Windows authentication to run the stored procedure, omit the -U and -P parameters and configure the scheduled task to run under the Windows credentials.






Click Finish to save the job.

If you want to enable the task history, click the Enable All Tasks History action on the right (if it is not enabled already), but beware that that will enable history for all tasks.




At this point the job is enabled and will run at the scheduled time forcing SQList to re-sync the tables you selected. You can check the logs in SQList Manager at the scheduled time to confirm the job worked.