Using TOP with SQL Server and Zoho

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
datasavvy
Posts: 1
Joined: Mon 08 Oct 2018 07:33

Using TOP with SQL Server and Zoho

Post by datasavvy » Mon 08 Oct 2018 07:44

Hello,

Is there a special syntax for retrieving the Top N rows of data in a table? I am using the ODBC Driver for Zoho CRM with SQL Server 2014. Same issue with SQL 2016 and Access.

When I use the statement without the ORDER BY clause, it returns all rows. SELECT TOP (5) FROM [ZOHO]...[Leads]

When I use the ORDER BY clause, I get the error below. SELECT TOP (5) FROM [ZOHO]...[Leads] ORDER BY "LEADID"

Error:
OLE DB provider "MSDASQL" for linked server "ZOHO" returned message "[Devart][ODBC][Zoho CRM]Unknown column 'Col1004'".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT "Tbl1002"."LEADID" "Col1004" FROM "Leads" "Tbl1002" ORDER BY "Col1004" ASC" against OLE DB provider "MSDASQL" for linked server "ZOHO".

It also does not work if I use TOP (5) *

Thanks.

MaximG
Devart Team
Posts: 1338
Joined: Mon 06 Jul 2015 11:34

Re: Using TOP with SQL Server and Zoho

Post by MaximG » Mon 08 Oct 2018 09:51

Our ODBC driver is designed with using SQLite engine, therefore to compose queries with our driver, you can use the syntax used in SQLite : http://www.sqlite.org/lang.html
You can limit the number of rows returned by the query in the following way:

Select * From OPENQUERY([ZOHO], 'Select * From Leads Order By LEADID Limit 5' )

Post Reply