[Err] ERROR: DB-Library error: DB #: 2404, DB Msg: Buffer overflow converting characters from client into server's character set, OS #: 0, OS Msg: Success, Level: 4

[502647092]

I try to access MSSQL database from a PostgreSQL database by using FreeTDS and tds_fdw,

but i Received this error
[Err] ERROR: DB-Library error: DB #: 2404, DB Msg: Buffer overflow converting characters from client into server's character set, OS #: 0, OS Msg: 成功, Level: 4

can you help me to Solve this problem

I am a Chinese My English is not very good if you can't Understand please contact me [email protected] Thank you!

[502647092]

i find the problem
because the mssql database has Special character like this (\ue77c) and (\ue863)
so tds_fdw return this error Buffer overflow converting characters from client into server's character set
but how to Solve this problem
Can you help me

[GeoffMontee]

This sounds like a character set might be incorrect somewhere. There are a few different places to check for character set mismatches when using tds_fdw:

1.) When you are using Microsoft SQL Server with a unicode character set, you need to set the tds_version tds_fdw foreign server option or the tds version FreeTDS option in freetds.conf to 7.0 or greater.

2.) The client charset FreeTDS option in freetds.conf.

3.) You need to set the correct character set for the database in PostgreSQL.

4.) You need to ensure that your PostgreSQL client has the proper character set. e.g. you can check with:

postgres=# SHOW client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

And you can change it with:

postgres=# SET NAMES 'UTF8';
SET

[502647092]

I think that encoding is correct
if query result not included special characters tds_fdw is running correct
but have special characters is running wrong
data is grab from web page so maybe have some special characters

[GeoffMontee]

What does your freetds.conf file look like?

Do you set tds_version for the foreign server?

What is the character set that the PostgreSQL database is configured to use?

[502647092]

tds_version is 7.1
PostgreSQL character set is UTF-8

[GeoffMontee]

Can you please show what your freetds.conf looks like?

[aorashi]

add to freetds.conf
string tds version = for appropriate server
and remove tds version in options block of create foreign server in database

[bolton1643]

@502647092 have you resolved the problem?

[Arief6684]

Help me please, I try to access MSSQL database from a PostgreSQL database by using FreeTDS and tds_fdw, if I am excute this query "SELECT code FROM public.sap_oitt;" but always I get Error like this

SQL Error [HV00L]: ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

[502647092]

@bolton1643 @Arief6684
Sorry I not resolved the problem
i sync table mssql and pgsql

[arenius]

I am getting the same issue:

NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
ERROR:  DB-Library error: DB #: 2403, DB Msg: Some character(s) could not be converted into client's character set.  Unconverted bytes were changed to question marks ('?'), OS #: 0, OS Msg: Success, Level: 4
Time: 16427.588 ms (00:16.428)

The postgres database is UTF-8:

 warehouse      | rob        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

The client encoding is UTF-8:

show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

My freetds.conf contains:

[GOVDB09]
    host	= GOVDB09
    port	= 1433
    tds version = 7.4
    client charset = UTF-8

From the freetds.log I had during the error:

token.c:2304:tds_process_row(): reading column 4
token.c:2049:tds_get_data: type 56, varint size 0
token.c:2110:tds_get_data(): wire column size is 4
token.c:2304:tds_process_row(): reading column 5
token.c:2049:tds_get_data: type 56, varint size 0
token.c:2110:tds_get_data(): wire column size is 4
util.c:156:Changed query state from READING to PENDING
buffering.h:306:buffer_transfer_bound_data(0x558f8ac15a60 4040 -1 0x558f8ac15a50 0)
dblib.c:2100:leaving dbnextrow() returning REG_ROW/MORE_ROWS
dblib.c:2018:dbnextrow(0x558f8ac15a50)
dblib.c:2031:dbnextrow() dbresults_state = 2 (_DB_RES_RESULTSET_ROWS)
token.c:540:tds_process_tokens(0x558f8ac189d0, 0x7ffcce8ec31c, (nil), 0x1508)
util.c:156:Changed query state from PENDING to READING
token.c:555:processing result tokens.  marker is  d1(ROW)
token.c:2304:tds_process_row(): reading column 0
token.c:2049:tds_get_data: type 127, varint size 0
token.c:2110:tds_get_data(): wire column size is 8
token.c:2304:tds_process_row(): reading column 1
token.c:2049:tds_get_data: type 38, varint size 1
token.c:2110:tds_get_data(): wire column size is 8
token.c:2304:tds_process_row(): reading column 2
token.c:2049:tds_get_data: type 38, varint size 1
token.c:2110:tds_get_data(): wire column size is 2
token.c:2304:tds_process_row(): reading column 3
token.c:2049:tds_get_data: type 35, varint size 4
token.c:2110:tds_get_data(): wire column size is 854
util.c:331:tdserror(0x558f8ac14890, 0x558f8ac189d0, 2403, 0)
dblib.c:7929:dbperror(0x558f8ac15a50, 2403, 0)
dblib.c:7981:2403: "Some character(s) could not be converted into client's character set.  Unconverted bytes were changed to question marks ('?')"

I'm not sure what to try next on this.

[solayunus]

I'm having a similar issue any solution to this yet

[peglegboy]

Non-printable Unicode characters 129, 141, 143, 144, and 157 are not compatible with tds_fdw.

Identify where the offending characters exist then purge them.

SELECT *
FROM mytable
WHERE mycolumn COLLATE Latin1_General_CI_AS
LIKE '%[' + CHAR(129)+ CHAR(141) + CHAR(143) + CHAR(144) + CHAR(157)+']%'

[DrorSegev]

We solved it by adding option 'character_set' 'UTF-8' on the foreign server options.
Worked with both 2019 and 2022 on AWS

[mike-eh2]

We solved it by adding option 'character_set' 'UTF-8' on the foreign server options.

Thank you! This was our solution for Aurora RDS & on-prem SQL server. To save others some googling:

ALTER SERVER [foreign_server_name] OPTIONS (ADD character_set 'UTF-8');