How to insert the special character in DB?

Hmm. I added iconv support to FreeTDS and in some sense am responsible for that error message. I have three questions and one suggestion.

1. What is the encoding of the target column, table_name.name? Does that encoding support the characters you're trying to insert? I bet not.

2. Does the client charset in freetds.conf match the character set reflected in locale(1)?

3. Are you sure you're not using the old TDS protocol version 4.1?

FreeTDS converts SQL text to UTF-16. When you interpolate the data into the SQL insert statement, FreeTDS converts the whole statement, data and all. If the client encoding is incorrectly described, the data can't be converted. If the server encoding can't represent the character, the data can't be converted. And if the protocol is ancient, there's no support for Unicode in sight.

It's hard to tell from your error message, but it looks to me like the client side succeeded and the server side failed. That is, FreeTDS correctly converted the data to UTF-16, but the server could not insert the data into the name column, because it's varchar, not nvarchar, and the "code page" for the database (or column, if specified) is not one that can represent those characters.

My suggestion is to use TDS_DUMP and join the FreeTDS mailing list. The TDS_DUMP log will answer all the questions I posed. And you'll get better support on the mailing list, because it's specialized.

Well you may ask why DBeaver seems to work. I don't know; I never heard of it until now. I can tell you that there are ways to insert data (for example, with parameterized queries in ODBC) where the server simply inserts the data verbatim. Strangely enough, it doesn't verify that they are valid for the declared encoding.

I've helped many people with problems like that. The database is set up for, say, CP-1252, but the data aren't correctly encoded. The application can insert mis-encoded data, and later retrieve them and render them correctly, slipping under the radar as it were. But let the DBA try to query the data or inspect them with normal admin tools, and they look funny. The server assumes the data in its database are encoded per the database definition. When that's not the case hilarity ensues!

If that's your case, you can easily test for it. Use DBeaver to insert the data. Use the SQL Server admin application, or good old isql, to retrieve it on Windows. Odds are you will not be amused.

Convert the utf-8 into utf-16 before inserting it into a nvarchar column.

And back to utf-8 when reading it.

Or use a varbinary column and store the plain utf-8 bytes in there. Storing utf-8 encoded data in a nvarchar column is wrong.

Can you try changing your code like this?

$insert_query = "INSERT INTO table_name (name) values(?)";my $sth = $dbh->prepare($insert_query);$sth->bind_param(1, $name, DBI::SQL_WVARCHAR);$sth->execute() or $DB_Error=$DBI::errstr;