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

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(at)Intuii(dot)com>
To: "'Adrian Klaver'" <adrian(dot)klaver(at)aklaver(dot)com>,
<pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com]
Sent: Monday, July 27, 2015 9:47 AM
To: Jens Sorensen <JensSorensen(at)Intuii(dot)com>; pgsql-odbc(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com