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

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?