Postgres Pro
Downloads
Home   >   mailing lists

Re: PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606. - Mailing list pgsql-odbc

From Raiford@labware.com
Subject Re: PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606.
Date
Msg-id OFA4C0CA79.1D04F4BD-ON85257E90.005D8607-85257E90.005D8F30@labware.com
Whole thread Raw
In response to Re: PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606.  ("Jens Sorensen (Intuii)" <JensSorensen@Intuii.com>)
List pgsql-odbc
Tree view
I believe your problem is that SQL Server is determining how to translate the data types. You might have better luck casting the column to varchar(max) (or to text) - see below.  Alternatively you can use the OLE DB provider for Postgres instead of using ODBC.  Or maybe switch over to SSIS so you can use ODBC directly without going through the OLE DB / ODBC wrapper MSDASQL.

Jon


INSERT http_events
SELECT *
FROM openrowset
(
'MSDASQL',
'Dsn=PostgreSQL35W;',
'Select  
     event_id,
     time_stamp,
     session_id,
     client_intf,
     server_intf,
     c_client_addr,
     s_client_addr,
     c_server_addr,
     s_server_addr,
     c_client_port,
     s_client_port,
     c_server_port,
     s_server_port,
     policy_id,
     username,
     hostname,
     request_id,
     method,
     cast(uri as varchar(max)),
     host,
     c2s_content_length,
     s2c_content_length,
     s2c_content_type,
     adblocker_blocked,
     adblocker_cookie_ident,
     adblocker_action,
     webfilter_reason,
     webfilter_category,
     webfilter_blocked,
     webfilter_flagged,
     sitefilter_reason,
     sitefilter_category,
     sitefilter_blocked,
     sitefilter_flagged,
     clam_clean,
     clam_name,
     virusblocker_clean,
     virusblocker_name
FROM reports.http_events AS log'
)




From:        "Jens Sorensen (Intuii)" <JensSorensen@Intuii.com>
To:        "'Adrian Klaver'" <adrian.klaver@aklaver.com>, <pgsql-odbc@postgresql.org>
Date:        07/28/2015 12:40 PM
Subject:        Re: [ODBC] PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606.
Sent by:        pgsql-odbc-owner@postgresql.org



Thanks,

I've replaced the 'text' column.

Here is the error messages when I use 'varchar(max)':

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

And here is the error messages when I use 'varchar(max)':

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

So unfortunately this does not appear to have made a difference.

Here is my current query on SQL Server:

INSERT http_events
SELECT *
FROM openrowset
(
'MSDASQL',
'Dsn=PostgreSQL35W;',
'Select  
     event_id,
     time_stamp,
     session_id,
     client_intf,
     server_intf,
     c_client_addr,
     s_client_addr,
     c_server_addr,
     s_server_addr,
     c_client_port,
     s_client_port,
     c_server_port,
     s_server_port,
     policy_id,
     username,
     hostname,
     request_id,
     method,
     uri,
     host,
     c2s_content_length,
     s2c_content_length,
     s2c_content_type,
     adblocker_blocked,
     adblocker_cookie_ident,
     adblocker_action,
     webfilter_reason,
     webfilter_category,
     webfilter_blocked,
     webfilter_flagged,
     sitefilter_reason,
     sitefilter_category,
     sitefilter_blocked,
     sitefilter_flagged,
     clam_clean,
     clam_name,
     virusblocker_clean,
     virusblocker_name
FROM reports.http_events AS log'
)

What additional information can I provide you that would be useful?


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, July 27, 2015 9:47 AM
To: Jens Sorensen <JensSorensen@Intuii.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606.

On 07/27/2015 09:16 AM, Jens Sorensen wrote:
> Hmm when I inserted the values into a table I believe it produced a
> column of nvarchar(4000). 4000 appears to be the max for nvarchar in
> SQL Server. However I changed the table to use type text instead, and
> it did not appear to affect the error message any.

First, as I understand it text is deprecated in SQL Server:

https://msdn.microsoft.com/en-us/library/ms187993.aspx

The 8000 limit you are hitting is seems to the upper limit of SQL Server
varchar() unless you specify max:

https://msdn.microsoft.com/en-us/library/ms176089.aspx

So I wonder if somewhere along the line varchar(8000) is being substituted for text()?

This will need a more detailed description of what you are doing to answer.



--
Adrian Klaver
adrian.klaver@aklaver.com



--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

pgsql-odbc by date:

Previous
From: "Jens Sorensen (Intuii)"
Date:
Subject: Re: PSQLODBC.DLL - The (maximum) expected data length is 8000, while the returned data length is 9606.
Next
From: jaime soler
Date:
Subject: Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client - SOLVED