# "String or binary data would be truncated" error with DB Writer node

**URL:** <https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986>\
**Category:** KNIME Analytics Platform\
**Created:** [March 24, 2020, 9:44am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986 "2020-03-24T09:44:46Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![XXA](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/x/7ea924/32.png) [@XXA](https://forum.knime.com/u/XXA)\
**Post date:** [March 24, 2020, 9:44am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/1 "2020-03-24T09:44:46Z")

</div>

Hi,

I’m trying to use DB writer to write table into sql server database. Some of my columns are in string with Chinese character, DB writing by mapping string to varchar is ok, but the Chinese charater is displayed like ??? in varchar format. I tried to map string to nvarchar, the DB writer reported “String or binary data would be truncated” error.

In the past when I was using the legacy database writer node to write string containing Chinese character in nvarchar format, everything is good.

So the question is:

1. Is there a way to set the length of the nvarchar format? I searched the error code, looks like it is caused by inserting data into a string column while the data size is more than the column size.
2. Probably above is not the real cause? Any idea on how to solve this?

Appreciate your kind suggestion  
thanks

Xiaoan

---

<div class="post-metadata">

**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\
**Post date:** [March 24, 2020, 11:22am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/2 "2020-03-24T11:22:59Z")

</div>

Hi,  
I think you have to specify the length of your nvarchars by specifying the type like this: nvarchar(max), where max is the maximum length of your strings. If max is not given, it defaults to 1. Please have a look at this website for more info:

> **[SQL Server NVARCHAR Data Type Overview](https://www.sqlservertutorial.net/sql-server-basics/sql-server-nvarchar/)**
>
> In this tutorial, you will learn how to use the SQL Server NVARCHAR data type to store variable-length, Unicode string data.

  
Kind regards  
Alexander

---

<div class="post-metadata">

**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\
**Post date:** [March 24, 2020, 2:24pm UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/3 "2020-03-24T14:24:39Z")

</div>

Hi there @XXA,

there is difference between old and new database framework regarding character length  
Think these topics might help you:

> [@ETL Help: Loading SAS Datasets to MS SQL Server](https://forum.knime.com/t/etl-help-loading-sas-datasets-to-ms-sql-server/21588/6):
>
> Hi Scott, I modified the workflow you shared with me and replace H2 with MS SQL. For some SAS data sets, the flow works perfectly. For others, I get this error: Error while adding rows #-1000 - #0, reason: String or binary data would be truncated. I did many search but couldn’t find a viable solution where and how to address this issue. Any thoughts/suggestions? Thank You, Helal

> [@DB insert - Error](https://forum.knime.com/t/db-insert-error/21007):
>
> Hello, I am trying to insert data to SQL DB but I faced below error. ERROR DB Insert 0:245 Execute failed: Error while adding rows #-1000 - #0, reason: Data truncation I changed all varchar to longvarcart but still the same issue. can someone help me to understand the issue and fix it. thanks

Br,  
Ivan

---

<div class="post-metadata">

**Author:** ![XXA](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/x/7ea924/32.png) [@XXA](https://forum.knime.com/u/XXA)\
**Post date:** [March 25, 2020, 5:04am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/4 "2020-03-25T05:04:55Z")

</div>

Hi Both,

Thanks for the suggestion. Indeed DB table creator can be used to modify size of nvarchars.

However the issue is still there and I tested with some simple string and discovered more of this issue:

- with a single Chinese character, DB writing is ok even with the default nvarchar size, only need to change the mapping to “String to NVARCHAR”
- if the cell contains two Chinese characters, DB Insert or DB writer failed, even when I set size of the nvarchar to (max)

Best,  
Xiaoan

 ![Picture1](https://forum-cdn.knime.com/uploads/default/original/2X/0/08844e815ecbbf4030d4572cd9deea36965b2d19.png)

---

<div class="post-metadata">

**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\
**Post date:** [March 25, 2020, 2:41pm UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/5 "2020-03-25T14:41:57Z")

</div>

Hi there @XXA,

seems to me even with max argument length is still one. Have you tried some number instead of max?

Br,  
Ivan

---

<div class="post-metadata">

**Author:** ![AlexanderFillbrunn](https://forum-cdn.knime.com/user_avatar/forum.knime.com/alexanderfillbrunn/32/357_2.png) [@AlexanderFillbrunn](https://forum.knime.com/u/AlexanderFillbrunn)\
**Post date:** [March 25, 2020, 3:55pm UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/6 "2020-03-25T15:55:53Z")

</div>

Yes, Max is just a placeholder! There must be an actual number.

---

<div class="post-metadata">

**Author:** ![XXA](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/x/7ea924/32.png) [@XXA](https://forum.knime.com/u/XXA)\
**Post date:** [March 26, 2020, 3:02am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/7 "2020-03-26T03:02:21Z")

</div>

I tried 255 or 1024 or 9999, still the same

---

<div class="post-metadata">

**Author:** ![tobias.koetter](https://forum-cdn.knime.com/user_avatar/forum.knime.com/tobias.koetter/32/56844_2.png) [@tobias.koetter](https://forum.knime.com/u/tobias.koetter)\
**Post date:** [March 26, 2020, 9:58am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/8 "2020-03-26T09:58:10Z")

</div>

Hello,  
I was able to write Chinese characters into our SQL db. However I had to change the following default settings to make it work:

When creating the table using the _DB Table Creator_ I changed the column type to nvarchar(255)

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/9/9bf8f2365958cc86382de4f1ca1395e89f0b4ecf.png)

In the _DB Insert_ node I changed the default type mapping for String types to NVARCHAR via the _Output Type Mapping_ tab

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/a/aa09f1fd03198bd1ae2b2c6cfc443e985981dc28.png)

If you mostly want to write NVARCHAR you can also change the default output type mapping in the _Microsoft SQL Server Connector_ node. All nodes that are connected to the connector node inherit its type mapping settings.

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/d/ddb4f6dcbd2c2b5cae7a5da8bf9918ee6256abb7.png)

For more information about the type mapping have a look at the [database documentation](https://docs.knime.com/latest/db_extension_guide/index.html#type_mapping).

I also used the official Microsoft JDBC driver which is available in KNIME as a separate plugin. For an installation instruction see [here](https://docs.knime.com/latest/db_extension_guide/index.html#db_plugin).  
Bye  
Tobias

---

<div class="post-metadata">

**Author:** ![XXA](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/x/7ea924/32.png) [@XXA](https://forum.knime.com/u/XXA)\
**Post date:** [March 27, 2020, 2:32am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/9 "2020-03-27T02:32:58Z")

</div>

I tested the same setting as yours, but still failed. Tested in both 4.1.1 and 4.0.2 version.

I will just skip using the new DB node to write Chinese strings, at least the legacy database writer works well…

thanks for your help and support

regards,  
Xiaoan

---

<div class="post-metadata">

**Author:** ![tobias.koetter](https://forum-cdn.knime.com/user_avatar/forum.knime.com/tobias.koetter/32/56844_2.png) [@tobias.koetter](https://forum.knime.com/u/tobias.koetter)\
**Post date:** [March 31, 2020, 9:53am UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/10 "2020-03-31T09:53:03Z")

</div>

Hello Xiaoan,  
could you please help to understand what the problem might be. Attached you can find the workflow I used for testing. I created one table with database type _varchar(max)_ which didn’t work since it returned as expected _???_ for the Chinese characters.  
However when I created a table with _nvarchar(max)_ I was able to write and read my example text. The workflow also contains a DB Query Reader that executes a statement which gets detailed information about the table structure from the database.  
Can you please check with your database if the example workflow runs as expected or adapt your workflow accordingly and also double check using the _DB Query Reader_ node that the column in your database table has the right type as shown below.

 ![image](https://forum-cdn.knime.com/uploads/default/original/2X/b/b3c81e30171cb039d49a541ab1759fa181c02599.png)

Thanks  
Tobias

P.s. You can use _max_ in the varchar/nvarchar definition whereas _max_ indicates that the maximum storage size is 2^30-1 characters (2 GB) should be used. For more details see [here](https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15).

[SQLServerChineseCharacter.knwf](https://forum.knime.com/uploads/short-url/xGF8hxqkRpmKxvM1gD5veWZ5Rha.knwf) (30.7 KB)

---

<div class="post-metadata">

**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\
**Post date:** [September 29, 2020, 9:53pm UTC](https://forum.knime.com/t/string-or-binary-data-would-be-truncated-error-with-db-writer-node/21986/11 "2020-09-29T21:53:12Z")

</div>

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
