Read data using Direct COQL Endpoint

Runs a direct Zoho CRM COQL query using the get_module_data_coql endpoint. Enter the full COQL statement in the sql_query parameter. The connector sends the query to Zoho's /coql API and automatically pages results when your query does not include a LIMIT clause.

Use this endpoint when you want full control over the COQL query, including selected fields, filters, joins, sorting, and aggregate expressions. For a guided parameter-based experience, use the get_module_data_coql_builder endpoint instead.

Tip: If you want to run native COQL directly without calling an endpoint with WITH(...), use the #DirectSql prefix. See the native COQL pass-through example below.

API v2 supports up to 200 rows per page. Newer API versions such as v7 or v8 support larger page sizes, up to 2000 rows per page. For the Emails module, use API v2 and keep PageSize at 200 or less.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

SELECT *
	FROM get_module_data_coql
	WITH(
		  sql_query = 'select Last_Name, First_Name, Company from Leads where Company like ''Test'' order by id desc'
		, Version = 'v8'
		, PageSize = '2000'
	)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_ZOHO_CRM_IN_GATEWAY], 'SELECT *
	FROM get_module_data_coql
	WITH(
		  sql_query = ''select Last_Name, First_Name, Company from Leads where Company like ''''Test'''' order by id desc''
		, Version = ''v8''
		, PageSize = ''2000''
	)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_ZOHO_CRM_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
	FROM get_module_data_coql
	WITH(
		  sql_query = ''select Last_Name, First_Name, Company from Leads where Company like ''''Test'''' order by id desc''
		, Version = ''v8''
		, PageSize = ''2000''
	)'
EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY]