polybase connection erros

2,950

SAP Managed Tags:
SAP SQL Anywhere

Hi,

I am trying to get a polybase connection going from SQL 2019 to sybase 17. The connection is working and I can see data, but its converting sql to ANSI. Example:

SELECT TOP (1000) *
FROM [polygrab].[dbo].[ExtEvent_test]

Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [SAP][ODBC Driver][SQL Anywhere]Syntax error near 'limit' on line 3 .

Here is my poly-base connection

CREATE EXTERNAL DATA SOURCE [TicketMaster] WITH (
    LOCATION = N'odbc://externalserver.com:50459'
    , CONNECTION_OPTIONS = 'DRIVER={SQL Anywhere 17};
    Host=externalserver.com:50459; 
    ServerName= <servername>;
    DatabaseName=<databasename>;'
    , CREDENTIAL = [PolyFriend2]
    )

What am I doing incorrectly ?

Here is the connection log: UID=user_name;PWD=********;DBN=database;ServerName= <serverName>;CON=SQL_DBC_<#>;LOG=c:\\log\\client.log;Host=externalserver.com:50459 09:56:13 Attempting to connect to a running server... 09:56:13 Attempting TCPIP connection (address externalserver:50459 found in sasrv.ini cache) 09:56:13 Looking for server with name <database_name> 09:56:13 Trying to find server at cached address externalserver:50459 without broadcasting 09:56:13 Found database server <database_name> on TCPIP link 09:56:13 Connected using client address externalserver:50459 09:56:13 Connected to server over TCPIP 09:56:13 Connected to SQL Anywhere Server version 17.0.10.5963 09:56:13 Application information: 09:56:13 IP=172.20.210.171 ;HOST=my_machine ;OSUSER=my_machine$;OS='Windows 8 Build 9200 ' ;EXE='C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Binn\\Polybase\\mpdwsvc.exe' ;PID=<#> ;THREAD=<#> ;VERSION=17.0.0.1062 ;API=ODBC ;TIMEZONEADJUSTMENT=-300 09:56:13 Connected to the server, attempting to connect to a running database... 09:56:13 [185313] Connected to database successfully 09:56:13 [185313] Client disconnected 09:56:13 [185313] Disconnected from server

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

SELECT TOP needs an ORDER BY, but that's not a syntax error.

adding order does not help, "Generic ODBC error: [SAP][ODBC Driver][SQL Anywhere]Syntax error near 'NULLS' on line 2."

I think its a driver issue or driver set up issue

FWIW [polygrab].[dbo].[ExtEvent_test] is not legal SQL Anywhere syntax because you can't specify a database (e.g., [polygrab].)

You might find ODBC logging helpful to see exactly what polybase is sending to SQL Anywhere... see the ODBC Administrator for logging setup.

Be sure to turn ODBC logging OFF when you're done, otherwise your hard drive will fill up 🙂

Does ODBC tracing work with an OLEDB driver, too? If not, you could at least use request level logging.

I'm not familiar with the MS SQL Polybase feature but as SQL Anywhere seems not be explicitly supported and there is support for generic ODBC drivers, is there a particular reason you are using/referencing the SQL Anywhere OLEDB driver (via Provider = 'SAOLEDB.17') and not the SQL Anywhere ODBC driver?

(Yes, I'm aware that the MS SQL Linked Server feature does rely on OLEDB, not ODBC.)

Since you are providing ODBC connection parameters, "Provider = ''SAOLEDB.17'';" doesn't make sense. Syntax error near 'limit' indicates to me that SQL Server is translating "TOP" to "limit" (which is the MySQL keyword). You are going to have to find some option to stop mapping of SQL keywords. If you omit TOP from your query, does everything else work?

Hi Thank you for looking into this. You are right the provider being stated does not help, it is not a required part of the connection and I left it in by mistake. I am adding the connection log to the question. Maybe it has some clues

I removed the "Provider = ''SAOLEDB.17'';" Everything works the same with out it.

If I don't use top, I can do a simple select statement. I can also use a where clause but not with date fields, if I do: Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [SAP][ODBC Driver][SQL Anywhere]Syntax error near '2019-07-25' on line 5 .

I think this is because dates are relatively a new data type.

There seems to be a PolyBase query plan viewer - if so, can you use that and find some details about the query sent to SQL Anywhere? (But as you are using ODBC, the ODBC tracing as suggested by Breck should also be of help.)

FWIW, date literals might be tricky because MS SQL does not always accept the ISO date format, AFAIK. At least I'm often running into troubles when querying from SQL Anywhere against MS SQL with German locale and date literals.

Enabling machine wide tracing in the ODBC data source administrator does not seem to trace polybase connections, I believe it is because they do not use the ODBC data source set up in ODBC data source administrator. They instead make their own, saved under external Resources in the polybase enabled database.

I was able to use polybase query plan viewer. here are the relevant details: StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementText="SELECT *
 FROM [polygrab].[dbo].[ExtEvent]
 WHERE upd_datetime < '2020-01-01'" StatementType="SELECT" ParameterizedText="(@1 varchar(8000))SELECT * FROM [polygrab].[dbo].[ExtEvent] WHERE [upd_datetime]<@1" QueryHash="0x84200FC7D2F8D414" QueryPlanHash="0x53368F42C59FAD79" RetrievedFromCache="true" SecurityPolicyApplied="false"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan NonParallelPlanReason="NoParallelForPDWCompilation" .. > <OutputList> list of columns </> <ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="'2020-01-01'" />

also I am able to use date! if i first declare it as date in a variable. In the query plan I can see it converts it to datetime2,the source column is date, but it somehow still works

So does that help you for your initial query with TOP?

it does not help with the top clause, and additionally using date as a variable is less ideal then I thought because I am not able to use the indexes on the sybase side because of the data conversion.

I meant does the plan viewer show the remote query so you can check the translation from MS SQL to SQL Anywhere?

No I don't think it does. It is helpful in looking at the behavior of the engine, where computation is happening what parts take the most time but not the translations.

In addition to the ODBC Tracing suggestion, you can turn on query logging at the SQLA server (-zr sql -o server.logfile). In this way, you can see what the query looked like when it ended up at the server.

It wasn't one of my brightest moments when I decided to leave "request level logging" out of my comment, the (stupid) reason being was that the message probably came from the driver, not the engine...

[SAP][ODBC Driver][SQL Anywhere]Syntax error near 'limit' on line 3

Anyway, the 'limit' gives it away as a possible polybase problem, where it uses a non-SQL-Anywhere keyword.

Request-level logging will probably confirm what polybase is actually sending.

I wonder if the old-school (non-polybase) technique would work? Let's dip into the archives...

Technique 4: Download Via Linked Server

The Linked Server feature was introduced in SQL Server 2000 
to allow ad-hoc queries in SQL Server to operate on a 
variety of external data sources via OLEDB and ODBC. 
Linked servers offer similar functionality to SQL Anywhere's 
proxy tables, but with different syntax: instead of 
referring to proxy tables, direct references are made 
to actual tables residing elsewhere from within SQL 
statements running in SQL Server.

As with proxy tables, all you need to use a linked 
server is ODBC access to the actual data and a few 
lines of SQL code. Figure 20 shows the code necessary 
for SQL Server to define a linked server to connect 
to SQL Anywhere and run an INSERT SELECT to copy all 
the data from the local table mss_source to the 
target table sa_target.

Figure 20: Download Via Linked Server

Context: SQL Server

1   EXEC sp_addlinkedserver
2      @server = 'mem',
3      @srvproduct = 'xxx',
4      @provider = 'MSDASQL',
5      @datasrc = 'sa_system_dsn' 
6   GO
7   
8   EXEC sp_addlinkedsrvlogin
9      @rmtsrvname = 'mem',
10     @useself = 'false',
11     @rmtuser = 'dba',
12     @rmtpassword = 'sql'
13  GO
14  
15  INSERT INTO mem..dba.sa_target
16  SELECT *
17    FROM mss_source
18  GO

<know-it-allmode>
FWIW, request level logging was already suggested yesterday, see the fifth comment:)
</know-it-all-mode>

So while I want to use the polybase technique to join tables remotely natively, I can use use linked server to check the driver. and using a linked server does work. I can use the TOP command, I can see the table definitions, searching the date column seems to work well.

The point is not to create ETLs but to point some reports directly to TicketMaster (sybase) Reporting server. currently we join TicketMaster data with our own and the complexity of the TSQL queries is intense (with multiple OPENQUERY statements), I just wanted to simplify it. But I think after a nearly a month of failed attempts that the polybase engine can not use this ODBC driver.

I don't have direct access to the SQLAserver 😞

Can you run a query via dbisql?

If so, run SELECT PROPERTY ( 'Name' ) to get the value to use in SERVER=.

If not, go yell at someone, tell them you can't get any work done if you can't run queries on the database 🙂

ODBC Tracing was also suggested. This you do have access to. What do you see in the trace log when you enable tracing? Look for SQLPrepare / SQLExecDirect calls.

I can run this in open query, I am using the server name in my connection string.

Accepted Solutions (0)

Answers (0)

Ask a Question

Top Q&A Solution Author

User

Count