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

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

From

"Jens Sorensen (Intuii)"

Date:

27 July 2015, 18:51:23

Hello,

I’m stuck.

I’m trying to import data from PostgreSQL running on an Untangle appliance (Untangle.com) to SQL Server, but can’t seem to overcome an issue with the following error:

Msg 7347, Level 16, State 1, Line 7

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.

Even after increasing the MaxLongVarcharSize as in the following select statement, I still get this same error with the value of 8000.

select * from openrowset('MSDASQL', 'Dsn=PostgreSQL35W;MaxLongVarcharSize=16000;', 'select * from reports.http_events')

I’ve also tried this one:

select * from openrowset('MSDASQL', 'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=10.5.55.1;port=5432;database=uvm;MaxLongVarChar=16000;',

‘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')

From ODBC setup:

From a “pg_dump” of schema:

-- Name: http_events; Type: TABLE; Schema: reports; Owner: postgres; Tablespace:

CREATE TABLE http_events (

event_id bigint NOT NULL,

time_stamp timestamp without time zone,

session_id bigint,

client_intf smallint,

server_intf smallint,

c_client_addr inet,

s_client_addr inet,

c_server_addr inet,

s_server_addr inet,

c_client_port integer,

s_client_port integer,

c_server_port integer,

s_server_port integer,

policy_id bigint,

username text,

hostname text,

request_id bigint,

method character(1),

uri text,

host text,

c2s_content_length bigint,

s2c_content_length bigint,

s2c_content_type text,

adblocker_blocked boolean,

adblocker_cookie_ident text,

adblocker_action character(1),

webfilter_reason character(1),

webfilter_category text,

webfilter_blocked boolean,

webfilter_flagged boolean,

sitefilter_reason character(1),

sitefilter_category text,

sitefilter_blocked boolean,

sitefilter_flagged boolean,

clam_clean boolean,

clam_name text,

virusblocker_clean boolean,

virusblocker_name text

);

Any advise appreciated!

Jens

Attachment

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

From

Adrian Klaver

Date:

27 July 2015, 19:05:17

On 07/25/2015 04:20 AM, Jens Sorensen (Intuii) wrote:
> Hello,
>
> I’m stuck.
>
> I’m trying to import data from PostgreSQL running on an Untangle
> appliance (Untangle.com) to SQL Server, but can’t seem to overcome an
> issue with the following error:
>
> *Msg 7347, Level 16, State 1, Line 7*
>
> *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 what is the size limit, if any, in the SQL Server field.

>
> Even after increasing the MaxLongVarcharSize as in the following select
> statement, I still get this same error with the value of 8000.
>
> select * from openrowset('MSDASQL',
> 'Dsn=PostgreSQL35W;MaxLongVarcharSize=16000;', 'select * from
> reports.http_events')
>
> I’ve also tried this one:
>
> select * from openrowset('MSDASQL', 'Driver=PostgreSQL
> Unicode(x64);uid=postgres;Server=10.5.55.1;port=5432;database=uvm;MaxLongVarChar=16000;',
>
>
> ‘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')
>
>  From ODBC setup:
>
>  From a “pg_dump” of schema:
>
> --
>
> -- Name: http_events; Type: TABLE; Schema: reports; Owner: postgres;
> Tablespace:
>
> --
>
> CREATE TABLE http_events (
>
>      event_id bigint NOT NULL,
>
>      time_stamp timestamp without time zone,
>
>      session_id bigint,
>
>      client_intf smallint,
>
>      server_intf smallint,
>
>      c_client_addr inet,
>
>      s_client_addr inet,
>
>      c_server_addr inet,
>
>      s_server_addr inet,
>
>      c_client_port integer,
>
>      s_client_port integer,
>
>      c_server_port integer,
>
>      s_server_port integer,
>
>      policy_id bigint,
>
>      username text,
>
>      hostname text,
>
>      request_id bigint,
>
>      method character(1),
>
>      uri text,
>
>      host text,
>
>      c2s_content_length bigint,
>
>      s2c_content_length bigint,
>
>      s2c_content_type text,
>
>      adblocker_blocked boolean,
>
>      adblocker_cookie_ident text,
>
>      adblocker_action character(1),
>
>      webfilter_reason character(1),
>
>      webfilter_category text,
>
>      webfilter_blocked boolean,
>
>      webfilter_flagged boolean,
>
>      sitefilter_reason character(1),
>
>      sitefilter_category text,
>
>      sitefilter_blocked boolean,
>
>      sitefilter_flagged boolean,
>
>      clam_clean boolean,
>
>      clam_name text,
>
>      virusblocker_clean boolean,
>
>      virusblocker_name text
>
> );
>
> Any advise appreciated!
>
> Jens
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Adrian Klaver

Date:

27 July 2015, 19:47:14

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

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

From

Jens Sorensen

Date:

28 July 2015, 00:37:57

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.

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

From

Adrian Klaver

Date:

28 July 2015, 16:17:02

On 07/27/2015 11:18 PM, Jens Sorensen (Intuii) wrote:
> 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:
>

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

You are doing a server to server transformation where psqlodbc is just
one component in the mix. It would be helpful to have the entire
transformation laid out in schematic for:

Postgres --> psqlodbc  ?????  --> SQL Server

Also where are the error message above coming from?

I see reference to Procedure RetrieveHttpEvents, so my guess somewhere
in the middle part of the diagram above.

Also when you say replaced 'text' column, where are you doing that, the
Postgres server, SQL server or somewhere else?

What is the actual CREATE TABLE definition for the tables on Postgres
and SQL Server?

Remember we are not looking at your set up directly, we only have what
you provide and the more you provide the better.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

"Jens Sorensen (Intuii)"

Date:

28 July 2015, 19:40:14

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

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

From

Raiford@labware.com

Date:

28 July 2015, 20:02:02

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.aspxThe 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.aspxSo 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

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

From

"Jens Sorensen (Intuii)"

Date:

29 July 2015, 18:31:30

Adrian>> You are doing a server to server transformation where psqlodbc is just one component in the mix. It would be helpful to have the entire transformation laid out in schematic for:

Adrian>> Postgres --> psqlodbc ????? --> SQL Server

I’m not sure how to lay this “out in schematic”, but hopefully what follows will provide the info you need.

Here is the setup I have for the PostgreSQL Unicode ODBC Driver (taken from the Windows Registry):

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PostgreSQL35W]

"Driver"="C:\\Program Files\\psqlODBC\\0903\\bin\\psqlodbc35w.dll"

"CommLog"="0"

"Debug"="0"

"Fetch"="100"

"Optimizer"="0"

"Ksqo"="1"

"UniqueIndex"="1"

"UseDeclareFetch"="1"

"UnknownSizes"="0"

"TextAsLongVarchar"="1"

"UnknownsAsLongVarchar"="1"

"BoolsAsChar"="1"

"Parse"="0"

"CancelAsFreeStmt"="0"

"MaxVarcharSize"="255"

"MaxLongVarcharSize"="15000"

"ExtraSysTablePrefixes"="dd_;"

"Description"="Untangle"

"Database"="uvm"

"Servername"="10.5.55.1"

"Port"="5432"

"Username"="postgres"

"UID"="postgres"

"Password"=""

"ReadOnly"="0"

"ShowOidColumn"="0"

"FakeOidIndex"="0"

"RowVersioning"="0"

"ShowSystemTables"="0"

"Protocol"="7.4-1"

"ConnSettings"=""

"DisallowPremature"="0"

"UpdatableCursors"="1"

"LFConversion"="1"

"TrueIsMinus1"="0"

"BI"="0"

"AB"="0"

"ByteaAsLongVarBinary"="0"

"UseServerSidePrepare"="1"

"LowerCaseIdentifier"="0"

"GssAuthUseGSS"="0"

"SSLmode"="disable"

"XaOpt"="1"

"KeepaliveTime"="-1"

"KeepaliveInterval"="-1"

"PreferLibpq"="-1"

Adrian>> Also where are the error message above coming from?

Adrian>> I see reference to Procedure RetrieveHttpEvents, so my guess somewhere in the middle part of the diagram above.

I’m not sure which diagram you are referring to. I am running the RetrieveHttpEvents stored procedure in Microsoft SQL Server Management Studio:

EXEC [dbo].[RetrieveHttpEvents]

Adrian>> Also when you say replaced 'text' column, where are you doing that, the Postgres server, SQL server or somewhere else?

I am changing the dbo.http_events table in SQL Server.

Adrian>> What is the actual CREATE TABLE definition for the tables on Postgres and SQL Server?

From a “pg_dump” of remote Untangle PostgreSQL schema:

-- Name: http_events; Type: TABLE; Schema: reports; Owner: postgres; Tablespace:

CREATE TABLE http_events (

event_id bigint NOT NULL,

time_stamp timestamp without time zone,

session_id bigint,

client_intf smallint,

server_intf smallint,

c_client_addr inet,

s_client_addr inet,

c_server_addr inet,

s_server_addr inet,

c_client_port integer,

s_client_port integer,

c_server_port integer,

s_server_port integer,

policy_id bigint,

username text,

hostname text,

request_id bigint,

method character(1),

uri text,

host text,

c2s_content_length bigint,

s2c_content_length bigint,

s2c_content_type text,

adblocker_blocked boolean,

adblocker_cookie_ident text,

adblocker_action character(1),

webfilter_reason character(1),

webfilter_category text,

webfilter_blocked boolean,

webfilter_flagged boolean,

sitefilter_reason character(1),

sitefilter_category text,

sitefilter_blocked boolean,

sitefilter_flagged boolean,

clam_clean boolean,

clam_name text,

virusblocker_clean boolean,

virusblocker_name text

);

Here is the CREATE script for the http_events table In SQL Server:

/****** Object: Table [dbo].[http_events]******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[http_events](

[event_id] [bigint] NOT NULL,

[time_stamp] [datetime2](7) NULL,

[session_id] [bigint] NULL,

[client_intf] [smallint] NULL,

[server_intf] [smallint] NULL,

[c_client_addr] [nvarchar](50) NULL,

[s_client_addr] [nvarchar](50) NULL,

[c_server_addr] [nvarchar](50) NULL,

[s_server_addr] [nvarchar](50) NULL,

[c_client_port] [int] NULL,

[s_client_port] [int] NULL,

[c_server_port] [int] NULL,

[s_server_port] [int] NULL,

[policy_id] [bigint] NULL,

[username] [nvarchar](1500) NULL,

[hostname] [nvarchar](1500) NULL,

[request_id] [bigint] NULL,

[method] [nchar](1) NULL,

[uri] [varchar](max) NULL,

[host] [nvarchar](1500) NULL,

[c2s_content_length] [bigint] NULL,

[s2c_content_length] [bigint] NULL,

[s2c_content_type] [nvarchar](1500) NULL,

[adblocker_blocked] [varchar](5) NULL,

[adblocker_cookie_ident] [nvarchar](1500) NULL,

[adblocker_action] [nchar](1) NULL,

[webfilter_reason] [nchar](1) NULL,

[webfilter_category] [nvarchar](1500) NULL,

[webfilter_blocked] [varchar](5) NULL,

[webfilter_flagged] [varchar](5) NULL,

[sitefilter_reason] [nchar](1) NULL,

[sitefilter_category] [nvarchar](1500) NULL,

[sitefilter_blocked] [varchar](5) NULL,

[sitefilter_flagged] [varchar](5) NULL,

[clam_clean] [varchar](5) NULL,

[clam_name] [nvarchar](1500) NULL,

[virusblocker_clean] [varchar](5) NULL,

[virusblocker_name] [nvarchar](1500) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_PADDING OFF

Here is the CREATE script for the RetrieveHttpEvents stored procedure in SQL Server:

/****** Object: StoredProcedure [dbo].[RetrieveHttpEvents] ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Description: Retrieves the HTTP Events Log entries from Untangle

-- =============================================

CREATE PROCEDURE [dbo].[RetrieveHttpEvents]

BEGIN

SET NOCOUNT ON;

declare

@NumRowsChanged int = 0;

declare

@ErrorCode int;

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'

)

END

Adrian>> Remember we are not looking at your set up directly, we only have what you provide and the more you provide the better.

I understand. Let me know if there is anything else I can provide you.

Jon>>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)

A first Test Query:

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(log.uri AS varchar(max)) AS 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'

)

Result:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "ERROR: syntax error at or near "max";

No query has been executed with that handle".

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

A second Test Query:

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(log.uri AS varchar(text)) AS 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'

)

Result:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "ERROR: syntax error at or near "text";

No query has been executed with that handle".

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

A third Test Query:

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(log.uri AS varchar(4001)) AS 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'

)

Result:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].uri" from OLE DB provider "MSDASQL" for linked server "(null)".

A last Test Query (which almost works):

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(log.uri AS varchar(4000)) AS 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'

)

Result:

(65261 row(s) affected)

Although this last query was “successful”, I assume it is chopping off the ends of any long values for uri. I can live with that, but it isn’t ideal.

I’m not sure how to pursue either of these alternative approaches. Can you provide any links that can help me get started?

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

From

Adrian Klaver

Date:

29 July 2015, 22:03:59

On 07/28/2015 03:13 PM, Jens Sorensen (Intuii) wrote:
> Adrian>> You are doing a server to server transformation where psqlodbc
> is just one component in the mix. It would be helpful to have the entire
> transformation laid out in schematic for:
>
> Adrian>> Postgres --> psqlodbc  ?????  --> SQL Server
>
> I’m not sure how to lay this “out in schematic”, but hopefully what
> follows will provide the info you need.

Heavily edited, to get to point:)


I have to believe your problem is in the below. From here:

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

"OPENROWSET(BULK...) assumes that, if not specified, the maximum length
of SQLCHAR, SQLNCHAR or SQLBINARY data does not exceed 8000 bytes."

I know you are not using BULK, but I am betting it making the same
assumption when you are not using.  Doing some searching I see the 8000
limit coming up often, however I have not found a definitive solution.
You might get an answer faster if you ask this on a SQL Server mailing
list/forum.

As to your cast problems:

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

"text and image Data Types

Automatic data type conversion is not supported for the text and image
data types. You can explicitly convert text data to character data, and
image data to binary or varbinary, but the maximum length is 8000 bytes.
If you try an incorrect conversion such as trying to convert a character
expression that includes letters to an int, SQL Server returns an error
message."

Which is what you are trying to do here:

CAST(log.uri AS varchar(max)) AS uri,


>
> -- =============================================
>
> -- Description: Retrieves the HTTP Events Log entries from Untangle
>
> -- =============================================
>
> CREATE PROCEDURE [dbo].[RetrieveHttpEvents]
>
> AS
>
> BEGIN
>
> SET NOCOUNT ON;
>
> declare
>
> @NumRowsChanged int = 0;
>
> declare
>
> @ErrorCode int;
>
> 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'
>
> )
>
> END
>
> GO
>
> Adrian>> Remember we are not looking at your set up directly, we only
> have what you provide and the more you provide the better.
>
> I understand. Let me know if there is anything else I can provide you.
>
> Jon>>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)
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Raiford@labware.com

Date:

30 July 2015, 17:42:08

Jon>> 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.
Jens>> I’m not sure how to pursue either of these alternative approaches. Can you provide any links that can help me get started?

I don't have any real examples to show you. I just found references to this when I was researching your error. You might be able to find some examples online.

By the way, instead of this:
CAST(log.uri AS varchar(text)) AS uri,

Try this:
CAST(log.uri AS text) AS uri,

Jon

From: "Jens Sorensen (Intuii)" <JensSorensen@Intuii.com>
To: <pgsql-odbc@postgresql.org>
Date: 07/29/2015 11:31 AM
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

Adrian>> You are doing a server to server transformation where psqlodbc is just one component in the mix. It would be helpful to have the entire transformation laid out in schematic for:
Adrian>> Postgres --> psqlodbc ????? --> SQL Server

I’m not sure how to lay this “out in schematic”, but hopefully what follows will provide the info you need.

Here is the setup I have for the PostgreSQL Unicode ODBC Driver (taken from the Windows Registry):

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PostgreSQL35W]
"Driver"="C:\\Program Files\\psqlODBC\\0903\\bin\\psqlodbc35w.dll"
"CommLog"="0"
"Debug"="0"
"Fetch"="100"
"Optimizer"="0"
"Ksqo"="1"
"UniqueIndex"="1"
"UseDeclareFetch"="1"
"UnknownSizes"="0"
"TextAsLongVarchar"="1"
"UnknownsAsLongVarchar"="1"
"BoolsAsChar"="1"
"Parse"="0"
"CancelAsFreeStmt"="0"
"MaxVarcharSize"="255"
"MaxLongVarcharSize"="15000"
"ExtraSysTablePrefixes"="dd_;"
"Description"="Untangle"
"Database"="uvm"
"Servername"="10.5.55.1"
"Port"="5432"
"Username"="postgres"
"UID"="postgres"
"Password"=""
"ReadOnly"="0"
"ShowOidColumn"="0"
"FakeOidIndex"="0"
"RowVersioning"="0"
"ShowSystemTables"="0"
"Protocol"="7.4-1"
"ConnSettings"=""
"DisallowPremature"="0"
"UpdatableCursors"="1"
"LFConversion"="1"
"TrueIsMinus1"="0"
"BI"="0"
"AB"="0"
"ByteaAsLongVarBinary"="0"
"UseServerSidePrepare"="1"
"LowerCaseIdentifier"="0"
"GssAuthUseGSS"="0"
"SSLmode"="disable"
"XaOpt"="1"
"KeepaliveTime"="-1"
"KeepaliveInterval"="-1"
"PreferLibpq"="-1"

Adrian>> Also where are the error message above coming from?
Adrian>> I see reference to Procedure RetrieveHttpEvents, so my guess somewhere in the middle part of the diagram above.

I’m not sure which diagram you are referring to. I am running the RetrieveHttpEvents stored procedure in Microsoft SQL Server Management Studio:

EXEC [dbo].[RetrieveHttpEvents]
GO

Adrian>> Also when you say replaced 'text' column, where are you doing that, the Postgres server, SQL server or somewhere else?

I am changing the dbo.http_events table in SQL Server.

Adrian>> What is the actual CREATE TABLE definition for the tables on Postgres and SQL Server?

From a “pg_dump” of remote Untangle PostgreSQL schema:
--
-- Name: http_events; Type: TABLE; Schema: reports; Owner: postgres; Tablespace:
--
CREATE TABLE http_events (
    event_id bigint NOT NULL,
    time_stamp timestamp without time zone,
    session_id bigint,
    client_intf smallint,
    server_intf smallint,
    c_client_addr inet,
    s_client_addr inet,
    c_server_addr inet,
    s_server_addr inet,
    c_client_port integer,
    s_client_port integer,
    c_server_port integer,
    s_server_port integer,
    policy_id bigint,
    username text,
    hostname text,
    request_id bigint,
    method character(1),
    uri text,
    host text,
    c2s_content_length bigint,
    s2c_content_length bigint,
    s2c_content_type text,
    adblocker_blocked boolean,
    adblocker_cookie_ident text,
    adblocker_action character(1),
    webfilter_reason character(1),
    webfilter_category text,
    webfilter_blocked boolean,
    webfilter_flagged boolean,
    sitefilter_reason character(1),
    sitefilter_category text,
    sitefilter_blocked boolean,
    sitefilter_flagged boolean,
    clam_clean boolean,
    clam_name text,
    virusblocker_clean boolean,
    virusblocker_name text
);

Here is the CREATE script for the http_events table In SQL Server:
/****** Object: Table [dbo].[http_events]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[http_events](
            [event_id] [bigint] NOT NULL,
            [time_stamp] [datetime2](7) NULL,
            [session_id] [bigint] NULL,
            [client_intf] [smallint] NULL,
            [server_intf] [smallint] NULL,
            [c_client_addr] [nvarchar](50) NULL,
            [s_client_addr] [nvarchar](50) NULL,
            [c_server_addr] [nvarchar](50) NULL,
            [s_server_addr] [nvarchar](50) NULL,
            [c_client_port] [int] NULL,
            [s_client_port] [int] NULL,
            [c_server_port] [int] NULL,
            [s_server_port] [int] NULL,
            [policy_id] [bigint] NULL,
            [username] [nvarchar](1500) NULL,
            [hostname] [nvarchar](1500) NULL,
            [request_id] [bigint] NULL,
            [method] [nchar](1) NULL,
            [uri] [varchar](max) NULL,
            [host] [nvarchar](1500) NULL,
            [c2s_content_length] [bigint] NULL,
            [s2c_content_length] [bigint] NULL,
            [s2c_content_type] [nvarchar](1500) NULL,
            [adblocker_blocked] [varchar](5) NULL,
            [adblocker_cookie_ident] [nvarchar](1500) NULL,
            [adblocker_action] [nchar](1) NULL,
            [webfilter_reason] [nchar](1) NULL,
            [webfilter_category] [nvarchar](1500) NULL,
            [webfilter_blocked] [varchar](5) NULL,
            [webfilter_flagged] [varchar](5) NULL,
            [sitefilter_reason] [nchar](1) NULL,
            [sitefilter_category] [nvarchar](1500) NULL,
            [sitefilter_blocked] [varchar](5) NULL,
            [sitefilter_flagged] [varchar](5) NULL,
            [clam_clean] [varchar](5) NULL,
            [clam_name] [nvarchar](1500) NULL,
            [virusblocker_clean] [varchar](5) NULL,
            [virusblocker_name] [nvarchar](1500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Here is the CREATE script for the RetrieveHttpEvents stored procedure in SQL Server:

/****** Object: StoredProcedure [dbo].[RetrieveHttpEvents] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Retrieves the HTTP Events Log entries from Untangle
-- =============================================
CREATE PROCEDURE [dbo].[RetrieveHttpEvents]
AS
BEGIN
SET NOCOUNT ON;
declare
@NumRowsChanged int = 0;
declare
@ErrorCode int;
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'
)
END
GO

Adrian>> Remember we are not looking at your set up directly, we only have what you provide and the more you provide the better.

I understand. Let me know if there is anything else I can provide you.

Jon>>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)

A first Test Query:
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(log.uri AS varchar(max)) AS 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'
)

Result:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "ERROR: syntax error at or near "max";
No query has been executed with that handle".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

A second Test Query:
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(log.uri AS varchar(text)) AS 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'
)

Result:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "ERROR: syntax error at or near "text";
No query has been executed with that handle".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

A third Test Query:
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(log.uri AS varchar(4001)) AS 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'
)

Result:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].uri" from OLE DB provider "MSDASQL" for linked server "(null)".

A last Test Query (which almost works):
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(log.uri AS varchar(4000)) AS 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'
)

Result:
(65261 row(s) affected)

Although this last query was “successful”, I assume it is chopping off the ends of any long values for uri. I can live with that, but it isn’t ideal.

Jon>> 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.

I’m not sure how to pursue either of these alternative approaches. Can you provide any links that can help me get started?