Page 1 of 1

Using TOP with SQL Server and Zoho

Posted: Mon 08 Oct 2018 07:44
by datasavvy
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.

Re: Using TOP with SQL Server and Zoho

Posted: Mon 08 Oct 2018 09:51
by MaximG
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' )