Getting error "String or binary data would be truncated" even for 'text' data type

Question

I have created a linked server (PostgreSQL). When I try to import data from SQL Server to linked server I get the following error:

String or binary data would be truncated

Even though I am familiar with this error, I have never faced it for the text data type. There is only text datatype in my table.

I would like to know is there are any limitation in linked server?

I have created a test table with a text column. When I try to insert from my linked server I can't insert more than 4000 characters. For testing purpose only I did this but actually I need to import data from SQL to p-sql.

Created following table in p-sql using pg admin

create table test_sql ( col text )

When I try to then inserted values

insert into linkedserver.database.schema.table (col) 
values ('4001 char')

but it fails to insert.

I have successfully created linked server with below query:

EXEC master.dbo.sp_addlinkedserver
@server = N'Test_server',
@srvproduct=N'PostgreSQL Unicode(x64)',
@provider=N'MSDASQL',
@provstr=N'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=######;database=test_db;pwd=password;SSLmode=disable;PORT=5432'

Answer 1

You are trying to put data into a column that is not wide enough for it, as the message suggets.

IIRC Postgre's text columns are open sized much like SQL Server's [N]VARCHAR(MAX) so I suspect there are some long values in there and the target column(s) in your table in SQL Server is a more limited character type (VARCHAR(50) or some such.

That is only a guess though. We can't be any more speicif without much more detail from you. What are the table definitions in Postgres and SQL Server? What tool(s) are you using for to migrate the data (SSIS, plain SQL, something else?)?