Polybase - Postgres - Numeric data type

Question

Using SQL Server 2019 CU13 Polybase to connect to a Postgres v14 database
Using Postgres ODBC Drivers

When the source postgres table has a column with numeric data type, like numeric(5,2)
the SELECT statement from the external table fails with error:

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: 17, active connections: 1'

CREATE EXTERNAL TABLE statement doesn't fail, only the SELECT from the external table fails

Linked server and Excel work fine with the same Postgres ODBC driver

Any advise?

Answer 1

Hi @DanP ,

I am not familiar with PostgresSQL, but I search this from google. We are using following steps to create an External Data Source for PostgreSQL Data.

1.Creating a Master Encryption Key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  

2.Create credentials for the external data source connected to PostgreSQL data

CREATE DATABASE SCOPED CREDENTIAL postgresql_creds  
WITH IDENTITY = 'username', SECRET = 'password';  

3.Create an external data source for PostgreSQL with PolyBase

CREATE EXTERNAL DATA SOURCE cdata_postgresql_source  
WITH (  
LOCATION = 'odbc://SERVERNAME[:PORT]',  
CONNECTION_OPTIONS = 'DSN=CData PostgreSQL Sys',  
-- PUSHDOWN = ON | OFF,  
CREDENTIAL = postgresql_creds  
);  

4.Use CREATE EXTERNAL TABLE statements to link to PostgreSQL data from your SQL Server instance

CREATE EXTERNAL TABLE Orders(  
ShipName [nvarchar](255) NULL,  
ShipCity [nvarchar](255) NULL,  
...  
) WITH (  
LOCATION='Orders',  
DATA_SOURCE=cdata_postgresql_source  
);  

Please also check this blog to find if you missed some key steps.

By the way, I think below error message is useful in your error message.

>SQL Error Info: Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] Driver does not support this function

Answer 2

I did all the steps,
There are no issues with querying other tables that do not have numeric datatype columns.

The only issue is when a table has numeric fields.
and I`m using Postgres ODBC Drivers (not CData drivers)

Answer 3

Got an answer from Microsoft,
the numeric data type issue can be solved with workaround below:

create external data source Postgres with
(
    location = 'odbc://localhost:5432',
    connection_options = 'Driver={PostgreSQL Unicode(x64)};PolyBaseOdbcSupportsSetDescRec=false',
    credential = postgresql_creds,
    pushdown = on
);

When creating an external data source specifying the PolyBaseOdbcSupportsSetDescRec=false will solve the numeric issues,
but it produces new issues, for example, unable to query a source table with numeric and json/jsonb columns :(

Answer 4

I'm trying to query DB2 through PolyBase with this code:

CREATE EXTERNAL DATA SOURCE Test_PolyBase  
	WITH (  
			LOCATION = 'odbc://192.168.1.44:50000'   
			,CONNECTION_OPTIONS = 'Driver={IBM DB2 ODBC DRIVER - DB2COPY1};  
									PolyBaseOdbcSupportsSetDescRec=false'  
			--,PUSHDOWN = ON  
			,CREDENTIAL = Test_PolyBase  
			);  

This is not working