1

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'
  • That particular wording seems to suggest the error comes from SQL Server. The truncation is probably happening on the SQL Server side. If the target data type is indeed text, maybe it gets incorrectly mapped in SQL Server to something else (and something too short). – Andriy M Sep 6 '16 at 17:58
  • You may need to look at the advanced settings of your psqlODBC driver. There's a checkbox called "Text as LongVarchar" that I think should be checked. This is how the dialog box looks like but I've no idea how you can access it. Hopefully some PostgreSQL experts can help. – Andriy M Sep 7 '16 at 6:51
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?)?

  • 1
    @David: The target table is on the linked server, which is PostgreSQL. SQL Server is the source. – Andriy M Sep 7 '16 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.