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.

What’s new in SQList v5

In this post I’m going to summarise all the updates to SQList in v5 releases (5.0, 5.1, 5.2, 5.3 and 5.4 at the time of writing)

It’s probably worth saying that upgrades from v4 are available to all SQList customers who subscribe to our preventive maintenance plan. If you like more information about his or a would like a quote for an upgrade please contact our sales department.

There have been a few potentially breaking changes in as part of the SQList v5 releases! Look out for this symbol 

Let’s have a look at the new features in the order in which they were added:

Archive SharePoint Data in SQL / Maintain deleted list items in SQL tables (v5.0)

We have added a setting that enables you to maintain deleted list items in the replicated SQL table. This can be useful it you want archive SharePoint list items to SQL server.

To disable the removal of deleted list items from the corresponding SQL table, just select the “Keep Deleted Items” column in the list selection when configuring a replication:

If a list has the “Keep deleted items” option selected, when an item is deleted it will not be removed from the SQL table, but only marked as deleted in the new column named:SQList.IsDeleted

Note:

  • this feature is available to SQList Professional edition only;
  • only items that are deleted from the time the feature is enabled are kept in the SQL table, items deleted previously are not exported;
  • if you drop the SQL table, items marked as deleted will not be recovered;
  • if an item does need to be deleted from the SharePoint list and SQL table, it has to be done manually for the SQL table;
  • if an item previously exported to the SQL table is updated and then deleted while SQList is not running (or the replication is failing), the item in the SQL table will be marked as deleted, but will contain the data as they were before the last update;
  • this feature must not be confused with versioning.

Added Windows authentication to individual SQL connections (v5.0)

We have added Windows Authentication as an option in each SQL database connection. With previous versions, Windows authentication was only possible using Trusted Authentication, which only allows the use of one set of Windows credentials (those used to run the SQList service) against all SQL databases. Each database connection can now have its own set of windows credentials.

To use Windows authentication to connect to a SQL database, choose “Windows authentication” from the Authentication Mode drop-down, when setting up a database connection in SQList Manager.

Two new columns have been added to the SQL tables (they will be automatically added to existing tables ):

  • SQList.Username: this column contains the name of the user, from the credentials, used to connect to the SQL Server, that last updated/inserted the row;
  • SQList.UTCUpdated: this column contains the UTC timestamp of the last update to the row.

Changed handling of non-standard column types (v5.0)

When SQList exports a list column into a SQL table, it assign the SQL type that most closely match the list column type (e.g. a “Text” with a maximum length or 30 will be exported as a NVARCHAR(30)).

If the type of the list column is not recognised (e.g. a custom column type), SQList will try to determine its type from the physical column name; if it still cannot determine the type, it will export it as a NVARCHAR(MAX).

Note: previous versions of SQList exports not recognised types as NVARCHAR(255) so, if you are upgrading be aware that the column in the SQL tables will me changed to the new type.

You can set the default length for these columns i the “Settings” tab, by changing the “Default length for NVARCHAR columns” value.


It is possible to map custom column types to specific SQL types by setting the “SPDB.CustomColumnMappings” value in the config file. This is an advanced setting, please contact us if you need to use it.

Increased precision for numbers “Displayed as percentage” (v5.0)

When SQList exports a “Number” column, it sets the number of decimals of the SQL column to the same length as it is defined in the SharePoint column.

However, when a SharePoint column is configured to be “Displayed as a percentage”, SharePoint stores the value divided by 100. This was causing a loss of precision under certain circumstances – e.g. a SharePoint column defined as a Number with 4 decimals, displayed as percentage, would store the number 1.2345 as 0.012345 in SharePoint and  as 0.0123 in the SQL table (loss of two decimals) requiring the column to be defined with 6 decimals to correct it.

We have now changed this behaviour so that two additional decimal positions are added to the SQL column for percentages so that no loss of precision occurs.

Note:

  • existing SQL columns defined to “Display as a percentage” will be altered to add the two new decimals 

Added SiteID to UNION view (v5.0)

The UNION view, created when exporting subsites, contained the name of the original table as one of its columns, to identify which table the row belongs. Using the name of the table did not prove ideal when joining UNION views, so we have removed that column (TableName) and replaced with the actual GUID of the site to which the item belongs.

  • a new column has been added to all tables, named WebID;
  • SQL joins between UNION views should now be done using the WebID and IDcolumns;
  • a new table is now created in the database, named SQList.Sites, which contains the GUIDs and Names of the exported sites;
  • the TableName column has been removed 

Custom name to table prefix (v5.0)

It is now possible to define a custom prefix to the tables generated by SQList.

In previous versions the name assigned to the site definition was also used at the prefix for the tables; this did not allow two different SharePoint sites to be exported to tables that have the same name.

While this worked well most of the times, there are some situations where you may want to use the same prefix for two different sites. For example, you may have separate replications configured for two versions of a SharePoint site, one development and one production. You may want to export these to separate SQL Server databases and generate tables with the same names.

New setting: “Abort all replications in one fails” (v5.0)

In previous versions, when one replication failed, SQList aborted all replications until the failing one was corrected. By default SQList now continues with the other replications. The old behaviour can still be resumed by selecting this new option.

All SharePoint system lists are now available (v5.0)

All lists in a SharePoint site are now available; in previous version, SQList was hiding some of the system lists.
Note: in you have existing replications that by default exports all lists, these new system lists will be exported 

Added chat support directly in SQList Manager  (v5.0)

If you are setting up a replication, and experience any difficulties, you can now open a chat window and contact us for assistance. If we are not online, you can leave a message, and we will get in touch as soon as possible.


Minor changes (v5.0)

  • added some additional checks and warning when setting up a replication;
  • added an audit feature that, if enabled, sends us anonymous usage statistics that will help us understand how you use our software and how we can make it ever better;
  • fixed some minor bugs…thank you to those of you who pointed them out!

Added setting for SQList system column separator (v5.1)

SQList system columns use a dot in their name (e.g. SQList.WebID, SQList.UTCUpdated, etc.), this is to ensure that their name cannot be the same as any other column in the table. However, this naming convention does not adhere to strict ANSI naming conventions.

I the dot is an issue, you can now change it (e.g. to an underscore) in the setting tab in SQList Manager. Note that for compatibility with previous versions of SQList, the default separator is a dot.


 Important:

  • If you do change this setting, ensure that you stop the service and drop all tables generated by SQList. Once you re-start the service, the tables will be re-created with the new naming convention.
  • This is a global setting and will affect all tables exported by SQList.

Export calculated columns based on the result type  (v5.1)

By default, SQList exports calculated columns as VARCHAR(MAX), regardless of the type of the result returned by the calculated column.

In this version, it is possible to export calculated columns as the type of their result (e.g. a calculated column that returns a number with 2 decimals, will be exported as a DECIMAL(18, 2)).


 Important:

  • If you do change this setting, SQList will try and convert the columns in the existing tables; if there are dirty data in the SharePoint list, the update of the table will fail. Please read “A warning about calculated columns” below, for more details.
  • This is a global setting and will affect all tables exported by SQList.

A warning about calculated columns  (v5.1)

It is possible for calculated columns (in fact, under certain circumstances, any column) to contain a value that is not compatible with the type defined as the column’s type. For example, SharePoint lets you define a calculated column using a formula that returns a string, but define its result’s type as number. In this case, if you configure SQList to use the result’s type as the column type, the export will fail because SQL Server will not allow the insertion of a string in a column defined as numeric.

An example:


Cross-site many-2-many lookup columns are now exported  (v5.1)

When a list contains a lookup to a list defined in a different SharePoint site (usually via a site column defined at super-site level), SQList tries and read the lookup list to retrieve it’s name, in order to create meaningful column names.

Previous versions of SQList throw a warning and skip the column when the credentials used to export the current site do not have access to the site in which the lookup list is defined.

Version 5.1 does instead export the column using a the column’s name, rather than the lookup list’s name, to generate the name for the SQL column.

 Important:

  • This change won’t affect your current replications because the additional columns and table will have their own unique names that won’t conflict with the existing ones.
  • This is a global setting and will affect all tables exported by SQList

New feature: On demand execution* (v5.2)

* Only available on selected editions

It is now possible to run a SQList synchronisation on demand, rather than as a service that continually checks for changes. This is useful when you do not need to have changes in SharePoint immediately replicated to SQL server, but rather at schedule intervals (e.g. once a day).

SQList can now run as a console application, so it can be executed in a command prompt window, a batch file, or as a schedule job (e.g. using Windows task scheduler).

To run a synchronisation, simply execute SQList as follow:

AxioWorks.SPDBService.exe singleCycle [logToConsole]

The parameter singleCycle instructs SQList to stop running after one synchronisation cycle, rather than keep checking for changes indefinitely. The optional parameter logToConsole redirect the log messages to the console, rather than the Windows event log (the default).

This is an example of an execution from command prompt:

A few notes:

  • The executable AxioWorks.SPDBService.exe is located in the SQList installation folder, by default: C:\Program Files (x86)\AxioWorks\AxioWorks SQList;
  • SQList must be run with administrator privileges;
  • If there are errors, SQList will not retry the replication but proceed to the end of the cycle.

New feature: Custom site filter*  (v5.2)

* Only available on selected editions

This feature allows you to define an additional filter to the sites exported by SQList. This filter consist of a text file named CustomSiteFilter.txt stored in the C:\ProgramData\AxioWorks\AxioWorks SQList folder, that contains the full URLs of the sites you want to export. This is an example:

Important: Note that this filter is applied after SQList has loaded the sites to export, as defined in SQList Manager, so you still need to define all the sites to export in SQList Manager.

An example scenario where this feature can be used is this: Let’s assume you have a SharePoint site with a large number of sub-sites, and you want to control programmatically which ones are exported. In that case you would configure SQList to export all sub-sites, and then you can have an internal procedure that updates the CustomSiteFilter.txt file with the one you want to export. In this case you may have 100s of sub-sites, but only export those that you select in the filter.
Note that the total sub-sites count against the license limits, not those exported via the custom filter.

Minor changes  (v5.2)

  • Enhanced change detection: we have made some minor adjustments to the change detection to handle some uncommon scenarios.

Export SharePoint lists that exceed the list view threshold (v5.3)

SharePoint limits the number of lookup columns retrieved in a single query; by default, this value is 8 in most versions of SharePoint, and 12 in SharePoint OnLine (see this article for the latest values); this value is called the lookup column threshold.

A list with 15 lookup columns, therefore, cannot be displayed in its entirety in the SharePoint UI, nor queried via the APIs, but only via a view which does not exceed the number of lookups defined by the threshold.

The threshold can be increased in Central Admin for SharePoint On-Prem versions, but it is not recommended. It is not possible to increase this threshold in SharePoint online in Office365.

Up to version 5.2, SQList could not export lists that exceeded the lookup column threshold, and the only work around it was to increase it.

In this version, we have changed the way SQList queries lists, so that it is no longer affected by the lookup threshold, making it possible to export without the need to adjust the threshold in Central Admin. A new setting is now available in the “Site Configuration”, where you can specify the value of the lookup threshold for the site, and SQList will adapt its queries to ensure it does not exceed that limit.

It is important to stress that all lookup values will be exported to the SQL table, not just those that are within the threshold.

Export of attachments, without binary data  (v5.3)

This feature allows you to export a list attachments, without the binary content; this feature is useful if you only need to export the list of attachments and their URLs, but not the actual document. Storage space in the SQL database is saved, and synchronisations are faster as the documents will not be exported.

To disable exports of attachment binaries, de-select the “Attach. binary content” option in the list selection screen, when configuring a replication.

 Quickly duplicate Site Configuration and Database connection  (v5.3)

This feature allows you to quickly create a copy of a Site definition or a Database connection. Useful, for example, when you have to set up a replication that uses the same credentials as another replication.

To duplicate a site definition (or a database connection), click the “Duplicate” icon on the list; this will present you with a copy of that site definition that you can edit and save as a new one.

Minor updates (v5.3)

  • Added SharePoint web service timeout to application configuration file: by default, the timeout for a request to a SharePoint web service is 120 seconds; if the connection is slow and there are large documents to download, this may not be enough. It is now possible to add a setting in the application configuration to set a longer timeout.
  • Removed the option to run SQList in trial mode without a license: previous versions of SQList ran without a license in Trial mode; in this mode, SQList only exported the first 100 items of a list. We discovered that this was causing more confusion than anything else, and gave the impression that SQList would not work. This option has been removed, and we invite you to download a fully working temporary license from our website instead.
  • Fixed minor bug in “Send event report” functionality: there was a minor bug, and it has been fixed.

Added feature to keep user-defined columns (5.4)

SQList mirrors the structure of a SQL table with the structure of its corresponding SharePoint lists. This means that when a new column is added to the list it gets added to the table. Likewise, when a column is removed from the list, it gets removed from the table.
Although we recommend not to alter the structure of the tables generated by SQList in any way, there are exceptional cases when you may need to add some columns to the table. This has become particularly true with temporal tables introduced with SQL Server 2016, which require two special columns to be added to the table.
You can read more about temporal tables here:
Although you cannot enable temporal tables via the SQList Manager interface, you can enable them directly in SQL Server . This article(1) describes the steps to enable system versioning on tables create by SQList:
There is a new global setting that can be used to instruct SQList not to drop certain columns for the SQL tables, when it synchronises their structure with their corresponding SharePoint list. Although this setting has been added mainly to handle the extra column required by SQL Server system versioning, it can be used for any columns you may want to add to the tables.
This new setting can be found in the “Settings” tab in SQList Manager; note that it is a global setting and therefore will affect all replications. By default, SQList will keep these columns:
  • SysStartTime
  • SysEndTime
  • SysPrimaryKey
(1) These columns are used in the article mentioned above

Added setting to manage throttling in SharePoint Online (5.4)

We introduced an additional configuration setting as part of the site definition, to manage throttling in SharePoint Online.
In the site definition details, there is a new setting named “Throttling control delays”; you can use this setting to define the length (in milliseconds) that SQList waits between requests to that particular site.
Note that while this setting is particularly aimed to SharePoint Online sites, it can be useful for an on-prem SharePoint farm as well, to reduce the amount of pressure put on the SharePoint WFEs.
For more information about throttling, please refer to this article:

Added list item’s version columns as part of standard columns (5.4)

As part of the standard columns, SQList now exports the the following columns that contain the list item’s version numbers:
  • owshiddenversion
  • _UIVersion
  • _UIVersionString

Improvements and fixes(5.4):

  • Improved connection to SharePoint web services: we have made several improvements to the way SQList connects to the SharePoint web services, which should result in less connections and authentications, to reduce the traffic to the SharePoint server itself and to the domain controller (where applicable);
  • Fixed SyncStatus table key: we fixed a problem that was causing unnecessary re-synchronisations when a replication was configured not to add the table prefix to the SQL table;
  • Added debug logging option to UI: we have added an option in SQList manager to enable Debug logging; previously this mode could only be enabled in the app.config file;
  • Reset change token when table is created: dropping a SQList table from the database will force a full re-synchronisation; previously you also had to reset the change token.

Click here to add your own text

Latest articles

We are looking for a Sales and Marketing Manager!

18 Jun 2021

We are looking for someone with experience in the SharePoint and BI arenas to take on the role of Sales and Marketing Manager. You will be able to […]
[read article]

Mover : Only Migration tool you will ever need to move your cloud content to SharePoint

11 Jun 2021

One of the biggest conundrum of any organization planning to move to SharePoint is how to move all their documents stored in various different […]
[read article]

New version of AxioWorks SQList released (7.0.0.0)

04 Jun 2021

A new version of AxioWorks SQList is out today! We have been really busy for the past few months, but here we are with a brand new release of […]
[read article]

AxioWorks Newsletter June 2021: May has been a month of new releases

01 Jun 2021

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

Form Configuration Editor: Lightweight and Built-in SharePoint List Form editor

16 May 2021

SharePoint Lists provides an easy and robust way of storing and manipulating data in SharePoint.  When you set up a new custom list, you also get OOTB […]
[read article]

AxioWorks Newsletter May 2021: April flew by faster than SpaceX

01 May 2021

Hello Reader, Welcome to another issue of our monthly Newsletter! Thank you for the great feedback on the past newsletters, our team has been working […]
[read article]

Bring Clarity to your SharePoint with MS Clarity

26 Apr 2021

Introduction There has been an exponential growth in SharePoint usage over the past few decades due to its vast set of capabilities to fulfill the […]
[read article]

AxioWorks Newsletter April 2021: A busy month!

01 Apr 2021

Welcome to our second Newsletter! Did you enjoy the first one? Or maybe you didn’t? Either way, do give us some feedback using the links at the […]
[read article]

MS Lists has finally landed in MS Teams

29 Mar 2021

During this Pandemic era, working remotely has significantly increased throughout the world and so is the usage of tools to facilitate collaboration […]
[read article]

AxioWorks Newsletter March 2021: Our first!

01 Mar 2021

We have just sent out our first newsletter and, being it our first one, we thought we dedicate a post to it. All future ones will go out just by […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter

*