0

I'm using SQL Server 2008 R2 with linked server to postgres 9.4.

My problem is: when I select column with data type text it shows me only XXX first chars and adds (...) on the end.

select description from codes

  1 x   x   2007-08-09
  2 x   x   2007-08-10
  3 x   x   2007-08-10
  4 x   x   2007-08-23
  5 x   x   2007-08-23
  6 x   x   2007-09-11
  7 x   x   2007-09-1 (...)

My second problem is when I try to get it into my SQL Server, I get this error:

Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server 'lnk_test' returned data that does not match expected data length for column '[MSDASQL].description'. The (maximum) expected data length is 8000, while the returned data length is 8832.

My script:

DECLARE @OPENQUERY NVARCHAR(4000) ,
    @TSQL NVARCHAR(4000) ,
    @LinkedServer NVARCHAR(4000);


SET @LinkedServer = 'lnk_test';
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''';
IF OBJECT_ID('codes', 'U') IS NOT NULL
    BEGIN
        DROP TABLE codes;
    END;
CREATE TABLE codes
    (
      code_id INT ,
      comment TEXT
    );

PRINT 'Getting stuff';

SET @TSQL = '
        select code_id, description from codes
                                            '')';

PRINT @OPENQUERY + @TSQL;

INSERT  INTO codes
        EXEC ( @OPENQUERY + @TSQL
            );

It drives me crazy...

  1. How can I select FULL value of text data type column in pgAdmin.
  2. Is there any way to get it through linked server?

ps. If I get answer to 1. probably my 2. will be solved by using XML (code_id, description as attributes) it take up to 2GB of data.

  • Don't use TEXT for comment, use NVARCHAR(MAX). – Mark Sinkinson Jul 1 '15 at 12:53
  • 1
    Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' for linked server 'lnk_test' returned data that does not match expected data length for column '[MSDASQL].comment'. The (maximum) expected data length is 8000, while the returned data length is 8076 – VoonArt Jul 1 '15 at 12:56
  • I've seen this before, a long time ago. I cannot verify the solution any more but it was an option on the connection string with MaxLongVarcharSize,TextAsLongVarchar and UnknownAsLongVarchar and stuff – Tom V Jul 1 '15 at 19:22

Your Answer

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

Browse other questions tagged or ask your own question.