[av_textblock size=” font_color=” color=”]
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!

[/av_textblock]

[av_textblock size=” font_color=” color=”]

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

[/av_textblock]

[av_textblock size=” font_color=” color=”]

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.

[/av_textblock]

[av_one_full first min_height=” vertical_alignment=” space=” custom_margin=” margin=’0px’ padding=’0px’ border=” border_color=” radius=’0px’ background_color=” src=” background_position=’top left’ background_repeat=’no-repeat’ animation=” mobile_display=”]
[av_textblock size=” font_color=” color=”]

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.

[/av_textblock]
[/av_one_full]

[av_textblock size=” font_color=” color=”]

• 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.

[/av_textblock]

[av_textblock size=” font_color=” color=”]
Click here to add your own text
[/av_textblock]