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
asked
20 Jan '20, 12:22
Crawly
26●1●1●5
accept rate:
0%
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...
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.