If, for any reason, a SQL table falls out of sync with its corresponding SharePoint list, it is often easier to force SQList to re-export that table from scratch rather than update individual list items to bring it back in sync.
The best way to do this is by erasing all existing data for the table in question, but keeping it's structure in the SQL database. This ensure that any views or stored procedures that use that table do not break (particularly when SCHEMABINDING is used.
SQList does not have a built-in option to erase a table's data, but this can be achieved with some simple SQL.
Let's take, as an example, the Products list shown below. This list has, amongst its columns, a multi-lookup to a metadata tree, and attachments.
When a table has multi-lookup columns or attachments, SQList creates additional tables to store these relationships and data. In our example, the Products list looks like this in the SQL database:
Now, let's assume we want to force SQList to re-export the Products list from scratch; to do that we need to follow these steps:
- Using SQList Manager, stop the SQList service;
- Delete the row in (SQList.Status) that saves the status of the
Portal365:Products table;
- Delete all data from all tables related to the Products list:
Portal365:Products, Portal365:Products._Attachments, and Portal365:Products.CategoryMetadataMulti;
- Using SQList Manager, re-start the service.
For your convenience, we have written a simple bit of TSQL that takes care of steps 2 and 3; see it copied below.
declare @schemaName as varchar(100);
declare @tableName as varchar(500);
set @schemaName = 'dbo'; <-- enter your schema's name here
set @tableName = 'Portal365:Products'; <-- enter the main table's name here
delete from [(SQList.SyncStatus)] where [TableName] = '[' + @schemaName + ']' + @tableName;
declare @name as varchar(500);
declare @sql as varchar(2000);
declare cur cursor for
select [name]
from sys.tables
where
([name] = @tableName or [name] like @tableName + '\.%' escape '\')
and
[schema_id] = (select [schema_id] from sys.schemas where [name] = @schemaName)
order by [name];
open cur;
fetch next from cur into @name;
while @@fetch_status = 0
begin
set @sql = 'delete from [' + @schemaName + '].[' + @name + '];';
execute(@sql);
fetch next from cur into @name;
end
close cur;
deallocate cur;