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.

• SysStartTime
• SysEndTime
• SysPrimaryKey

• 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