KNIME and MS-SQL

[DiaAzul]

Does anyone have any best practice guidance on using KNIME with Microsoft SQL server?

In particular, I have been having trouble with:

1/ Using a KNIME database Join node with MSSQL is case insensitive, whereas the corresponding non-database KNIME join node is case sensitive. It appears that MSSQL is configurable using collations, but it is not obvious from within KNIME how the database will perform the matching (case sensitive/insensitive) or how to control it from within KNIME so that calculations are predictable. This makes migration from native KNIME tables to computation on the database a risky transition that needs significant amounts of checking to ensure processing is consistent across deployments scenarios.

2/ When writing a table out to MSSQL using the Database Writer it is very picky with respect to field names, even for field names which have been imported using the Database Reader and then immediately written back out using the Writer. Examples would be field starting with a number, which work fine in MSSQL, but the Writer struggles with them as it doesn't escape the field names fully. I have also had trouble writing fields where the fieldname is similar to other names in the table, though not able to pin down precisely what the problem actually is [usually end up renaming fields until the problem goes away].

Any best practice guidance on using KNIME with MSSQL (or any database) would be much appreciated.

[tobias.koetter]

Hi,

1.) It depends on your database setup if the joining is performed case sensitive or insensitive. If you want to change this on the fly within KNIME you can use the Database Query node which gets a query as input (which could be your first table of the join) and allows you to write any SQL query using this input query.

 

2.) KNIME right now only quotes column names that contain a space. This is something we will change with the next version of the db framework on which we are working right now. So you might want to avoid column names that do not start with a character.

Bye

Tobias