On Sunday, March 20, 2022, 01:27:01 PM PDT, Matthew Reeves <bytemyzer@yahoo.com> wrote:
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