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
intothis 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
credentialand external data source have all been created, and external table links work as expected, with the exception
ofany tables containing numeric or decimal fields (ex. numeric(18,2), decimal(18,2)). When the MS-SQL-Server instance
attemptsto 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
ODBCerror: 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][ODBCDriver 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-SQLside, as an INT type. This works for any numeric fields where the scale is 0 (ex. numeric(10,0)). However, for
anyof the tables from the ERP financial module, where fields are defined as numeric(18,2), for example, this is not a
viablesolution.
I look forward to hearing from you,
Matthew