Known Issue: "Invalid column name 'connectiontype'" During FME Flow Backup

Issue

When attempting to create a backup for an FME Flow instance that utilizes SQL Server or Oracle for the system database, the following error may be displayed in the UI or in the backup task log:
 

SQL Query SELECT ncid, connectionname, connectiontype, servicename, username FROM fme_nc_namedconnections failed. Error: Invalid column name 'connectiontype'.

Cause

The FME Flow database has a server or database-level collation applied that causes the database to handle column names in a case-sensitive manner. The SQL Server and Oracle createDB.sql scripts that are shipped with FME Flow create the column in question with the name ‘connectionType’ (not ‘connectiontype’), which causes this error.

If you are reading this article before running one of these database creation scripts, you can avoid this issue entirely by editing the column name before running the script - simply search for the ‘fme_nc_namedconnections’ table and change the ‘T’ in the ‘connectionType’ column from uppercase to lowercase.

Workarounds

Option 1

Change the collation of your Flow database to one that is case-insensitive. This can usually be determined by the naming convention of the collation setting (CS = case-sensitive, CI = case-insensitive). The screenshot below shows an example of a collation setting in SQL Server that is case-sensitive. Make sure to select a collation based on your respective language.

Option 2

Rename the column in the Flow database from ‘connectionType’ to ‘connectiontype’. Please reference the documentation for your database provider for the exact syntax required to apply this change.