Problem with Converting string to nvarchar into database

[Rawlsjohnson]

Hello KNIME Team,

I am using knime for an ETL process and I am having trouble changing the data type from string to nvarchar when loading data into a new MS SQL table in the database. Varchar seems to work fine when I execute the workflow however I need to have specific columns to be nvarchar(255) so they can be a foreign key to our dimension tables in the database.

The error I am getting is:
“Execute failed: (“AbstractMethodError”): null”.

If anyone can help me find a work around or how to fix this error that would be great.

[bruno29a]

Hi @Rawlsjohnson and welcome to the Knime Community.

What node are you using to load the data into your MSSQL table? If it’s the DB Writer, it has an option for Output Type Mapping.

The Microsoft SQL Server Connector node also has this option. It allows you to map String to different types, including nvarchar.

Alternatively, if these options do not work, once your operation (loading) is done, you can always change the column type via the DB SQL Executor where you can do an ALTER TABLE on that table.

[Rawlsjohnson]

Hello @bruno29a. If I change the the output type mapping to nvarchar for strings or even just a single column it gives me the "“Execute failed: (“AbstractMethodError”): null”. I am using a DB Writer and I also tried db insert and also tried changing the output type at MS SQL server connector.

I just tried the DB SQL Executor which works on changing the column names but if I want to append data to an existing table with columns that have data type “nvarchar”, it gives me the same error.

[Rawlsjohnson]

Still having this issue. The DB SQL Executor method works however it is not very efficient when loading data into an already existing table.

[tobias.koetter]

Hello @Rawlsjohnson ,

this is a problem with the jTDS driver which does not support NVARCHARs. Can you please install the official driver as described here: KNIME Database Extension Guide
Once the official driver is installed make sure to select it in the Microsoft SQL Server Connector node

Also make sure that you either map String columns in general to NVARCHAR or dedicated columns via the Output Type Mapping tab of the DB manipulation node

Bye
Tobias

[Rawlsjohnson]

Hello Tobias,

Thank you for helping out. I seem to be getting an error when I use the driver name you highlighted when using native authentication. Does this driver support Microsoft authentication? I tried adding a microsoft authentication node before the MS Sql server node and got another error.

[izaychik63]

To make it work, on JDBC Parameters tab try to add parameter: integratedSecurity true.

[Rawlsjohnson]

Thank you that worked however the nvarchar thing still gives me an error.

Error while adding rows #-1000 - #0, reason: String or binary data would be truncated.