You can translate the question and the replies:

Errors with ODBC client connection from SQL Server Polybase

Hi-- My use-case requires me to establish a Polybase connection from SQL Server using ODBC driver. I have successfuly followed instructions on: https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-odbc-generic?view=sql-server-ver15 to accomplish that with other ODBC drivers. With DenodoODBC all steps work up until the point of actually querying (ie. I am able to see all the columns of my external table in the Object Explorer). SELECT * query returns the following error: Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [Microsoft][ODBC Driver Manager] Driver does not support this function . Interestingly it is the exact same error I get when I was trying to do the same with open source PostgreSQL ODBC driver, in addition, that driver's ODBC UI being very similar to yours -- same ODBC driver codebase? Microsoft does not like it for Polybase. With Polybase I was successful in using Devart PostgreSQL ODBC driver as well as Progress DataDirect PostgreSQL driver to connect to PostgreSQL. Best Regards,
user
27-05-2020 06:31:31 -0400

4 Answers

Hi, I have found this error can occur when the type of the fields in the external table are numeric. Changing the definition of the external table fields from numeric to int fixes the issue. Hope this helps!
Denodo Team
27-05-2020 14:14:37 -0400
Hi -- this is my code, believe none of the fields are numeric: CREATE EXTERNAL DATA SOURCE external_pg5 WITH ( LOCATION = 'odbc://192.168.0.169', CONNECTION_OPTIONS = 'Driver={DenodoODBC Unicode(x64)}; DSN = PG5', PUSHDOWN = ON, CREDENTIAL = credential_deno ); GO CREATE EXTERNAL TABLE payment5( payment_id INT NOT NULL, customer_id SMALLINT NOT NULL, staff_id SMALLINT NOT NULL, rental_id INT NOT NULL, amount DECIMAL(5, 2) NOT NULL, payment_date DATETIME2(6) NOT NULL ) WITH ( LOCATION='payment', DATA_SOURCE=external_pg5 ); GO SELECT * FROM dbo.payment5 GO
user
27-05-2020 14:45:41 -0400
Hi, I have been able to use VDP Server as a data source for Polybase, by specifying the location and the VDP ODBC port number, by default 9996. Hope this helps.
Denodo Team
01-06-2020 17:55:13 -0400
Actually first suggestion works! I misunderstood that numeric type change suggestion was in reference to EXTERNAL TABLE definition in MSSQL (which had no numeric fields) however in my underlying source table in PostgreSQL field 'amount' was in fact numeric type. I have changed it to float8 in PostgreSQL and FLOAT(53) in MSSQL EXTERNAL TABLE definition and the query now works through Denodo platform. Thanks.
user
02-06-2020 03:49:04 -0400
You must sign in to add an answer. If you do not have an account, you can register here