public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matthew Reeves <[email protected]>
To: [email protected]
Subject: PostgreSQL via PolyBase (MS-SQL) - Numeric Data Type Issue
Date: Sun, 20 Mar 2022 20:26:45 +0000 (UTC)
Message-ID: <[email protected]> (raw)
References: <[email protected]>

Hello, everyone,

I am hopeful that there is someone in the PG community who can help with this one.

Our company has a MS-SQL data-warehouse, and we have a requirement to incorporate data from a PostgreSQL ERP database into this environment.  We have elected to do so via the MS-SQL PolyBase component.

We have installed the psqlODBC drivers (psqlodbc_13_02_0000) on the MS-SQL-Server.  The master key, database scoped credential and external data source have all been created, and external table links work as expected, with the exception of any tables containing numeric or decimal fields (ex. numeric(18,2), decimal(18,2)).  When the MS-SQL-Server instance attempts to execute a query on such a table, the following error is returned:


Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: OdbcBufferReader.ReadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, 'Error calling: SQLSetDescRec(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol, (SQLSMALLINT)column->odbcReadType, 0, column->valueLength, (SQLSMALLINT)column->precision, (SQLSMALLINT)column->scale, (SQLPOINTER)(pBuffer + column->valueOffset), (SQLLEN *)indPtr, (SQLLEN *)indPtr), SQL return code: -1 | SQL Error Info:  Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] Driver does not support this function | Error calling: pReadConn->ReadBuffer(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, number: 566, active connections: 7', Connection String: DSN=POSTGRESQLERP.


The only workaround that I have seen posted on the web thus far is to map the PostgreSQL numeric data type, on the MS-SQL side, as an INT type.  This works for any numeric fields where the scale is 0 (ex. numeric(10,0)).  However, for any of the tables from the ERP financial module, where fields are defined as numeric(18,2), for example, this is not a viable solution.


I look forward to hearing from you,

Matthew

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: PostgreSQL via PolyBase (MS-SQL) - Numeric Data Type Issue
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox