RE: Linked server data length issue

Anthony (Anthony@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Erland, I'm linked to a Postgres DB. I've pulled many other queries
through Openquery and never had this issue until now. Thanks for your
help!

I've no experience with Postgres or its OLE DB provider, so I cannot not
help you very much. I only asked which RDBMS you are using, because that is
an essential piece of information. When you did not say anything, it was
reasonable to assume that you were connecting to another SQL Server
instance.

As I said, I cannot help you that much, but I can give you the theoretical
background for the error: "the maximum expected data length is 255,
while the returned data length is 321." When you run a query to a linked
server, SQL Server first interrogates the OLE DB provider for the data
source about the metadata the query will return. The OLE DB provider in
this case apparently reports that the data type of a certain column is
varchar(255). When an actual value which violates this types comes over
the value, SQL Server considers this a breach of contract, and fails the
query.

I've here talked about an OLE DB provider. I don't know if there is an
OLE DB provider for Postgres, of if you use the MSDASQL provider, OLE DB
over ODBC. In the latter case, the culprit is the ODBC driver for Postgres.

The best advice I can give is to see if there are any later versions of
the driver/provider for Postgres to see if it addresses the issue. The
error is clearly on that side.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages
Loading