Linked Server Truncates Columns to 255 Chars
Roger McMahon
Feature: Linked Server.
Using:
Provider = Microsoft OLE DB Provider for ODBC Drivers
Data Source: an ODBC System DSN
The linked server is created successfully. But queries from the Query
Analyzer to tables on the linked servers are truncated: only the first
approx 255 characters of each column are returned. Using MS-Excel with
the same ODBC System DSN returns all of the data, so it is at the
SQL-Server Linked Server level that the truncation is occurring.
Is this a bug, or is it a known limitation in the SQL-Server Linked
Server? Any workarounds?
This has been observed when linking to either Informix IDS V7.31 (using
the INTERSOLV V3.10 32-BIT SequeLink ODBC driver) and Lotus Notes V4.5
(using the Lotus NotesSQL Driver V2.06).
For the Linked Server to Informix, where values in an informix
char(1000) column are being truncated to 255 characters, I ran the
'sp_columns_ex' procedure. That listed it as a type char with a
COLUMN_SIZE, BUFFER_LENGTH and CHAR_OCTET_LENGTH all of 1000.
For the Linked Server to Lotus Notes, it is a Lotus Notes Rich Text
field that is being truncated. Different settings for the 'Max Length of
Rich Text Fields' setting in the 'Lotus NotesSQL ODBC Setup' have been
tried, including the default of 512, but it is still truncated at approx
255 chars. 'sp_columns_ex' on the Linked Server indicated that these
fields were type varchar with a COLUMN_SIZE, BUFFER_LENGTH and
CHAR_OCTET_LENGTH all of 512 (where 512 is the 'Max Length of Rich Text
Fields' setting).
Tibor Karaszi
Did you try to increase the setting in Query Analyzer (Query, Current
Connection Options, Advanced)?
--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.
Roger McMahon <roger....@bacon-woodrow.com> wrote in message
news:DD8C7CE3FFD94B4283A804C7D7B61FB694D4A5@CPMSFTMSGV22...
> Version: SQL-Server V7 with SP1.
> Feature: Linked Server.
> Using:
> Provider = Microsoft OLE DB Provider for ODBC Drivers
> Data Source: an ODBC System DSN
>
> The linked server is created successfully. But queries from the Query
> Analyzer to tables on the linked servers are truncated: only the first
> approx 255 characters of each column are returned. Using MS-Excel with
> the same ODBC System DSN returns all of the data, so it is at the
> SQL-Server Linked Server level that the truncation is occurring.
>
> Is this a bug, or is it a known limitation in the SQL-Server Linked
> Server? Any workarounds?
>
<snip>