Version 5.1 of SQList is a minor update from ver. 5.0.x, you can therefore upgrade for free if you have a licensed version 5.x. There are no breaking changes in this upgrade.
See below a summary of the changes.
Added setting for SQList system column separator
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
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
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
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