SQL Server guide

Search records using query

Searches for tickets, organizations, users, or groups using a query expression. This example demonstrates using the get_search endpoint with various filter options and sort parameters. You can filter by type, status, tags, dates, and more.

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_search --limited to 1000 rows max
--OR
--FROM get_search_export --returns more than 1000 rows but no sorting or extra column allowed
WITH(
       filtertype='' --can be ticket, organization, user, group. If you set here then no need to put in query (e.g. query='type:ticket ...'). This option is ignored for get_search_Export.
	  ,sort_by='updated_at' --created_at, status, priority, ticket_type. This option is ignored for get_search_Export.
	  ,sort_order='asc'  --desc (not valid for get_search_export). This option is ignored for get_search_Export.

    --read this for more information 
	--https://support.zendesk.com/hc/en-us/articles/4408883318554-Searching-users-groups-and-organizations
	--https://support.zendesk.com/hc/en-us/articles/4408886879258-Zendesk-Support-search-reference#topic_crj_yev_uc
	--https://support.zendesk.com/hc/en-us/articles/4408882086298-Searching-tickets
	
      ,query='type:ticket subject:"Upsert Destination"'   --search tickets where subject contains phrase "Upsert Destination" (special characters like comma / dash etc ignored in match)
	--,query='type:ticket 519872'   --search ticket with id 519872
	--,query='type:ticket tags:solved,odbc_powerpack updated>1months' --search tickets where two tags set solved,odbc_powerpack and updated in last one month
	--,query='type:ticket -tags:odbc_powerpack updated>1months' --search tickets where where tags not like odbc_powerpack and updated in last one month
	--,query='type:ticket status<solved created>1months' --search tickets with status not solved and created in last one month
	--,query='type:ticket created:2014-08-01' --search tickets created on 2014-08-01 (UTC)
	--,query='type:ticket created>2014-08-01T10:30:00Z created<2014-08-01T12:00:00Z' --search tickets created between two datetime (UTC time)
	--,query='type:ticket requester:amy@mondocam.com' --search tickets where requester is amy@mondocam.com
	--,query='type:ticket description:defective' --search tickets where decription contains word "defective"
	--,query='type:ticket description:"product is defective"' --search tickets where decription contains phrase  "product is defective"
	--,query='type:ticket priority>low' --search tickets where priority greater than low (e.g. medium or high)"
	
	--,query='ZappySys' --phrase match (no type): anywhere in ticket / organization / user contains ZappySys word - If filtertype parameter supplied then only those type included
	--,query='ZappySys*' --phrase match (no type): wildcard usage
	--,query='type:user "ZappySys"' --search users with term "ZappySys" anywhere in text properties
    --,query='type:user email:"@zappysys"' --search users with term "@ZappySys.com" in email field
	--,query='type:user name:"zappysys"' --search users with term "ZappySys" in name field
	--,query='type:user details:"zappysys"' --search users with term "ZappySys" in details field
    --,query='type:organization "ZappySys LLC"' --phrase match: organization name contains ZappySys LLC
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_ZENDESK_IN_GATEWAY], 'SELECT *
FROM get_search --limited to 1000 rows max
--OR
--FROM get_search_export --returns more than 1000 rows but no sorting or extra column allowed
WITH(
       filtertype='''' --can be ticket, organization, user, group. If you set here then no need to put in query (e.g. query=''type:ticket ...''). This option is ignored for get_search_Export.
	  ,sort_by=''updated_at'' --created_at, status, priority, ticket_type. This option is ignored for get_search_Export.
	  ,sort_order=''asc''  --desc (not valid for get_search_export). This option is ignored for get_search_Export.

    --read this for more information 
	--https://support.zendesk.com/hc/en-us/articles/4408883318554-Searching-users-groups-and-organizations
	--https://support.zendesk.com/hc/en-us/articles/4408886879258-Zendesk-Support-search-reference#topic_crj_yev_uc
	--https://support.zendesk.com/hc/en-us/articles/4408882086298-Searching-tickets
	
      ,query=''type:ticket subject:"Upsert Destination"''   --search tickets where subject contains phrase "Upsert Destination" (special characters like comma / dash etc ignored in match)
	--,query=''type:ticket 519872''   --search ticket with id 519872
	--,query=''type:ticket tags:solved,odbc_powerpack updated>1months'' --search tickets where two tags set solved,odbc_powerpack and updated in last one month
	--,query=''type:ticket -tags:odbc_powerpack updated>1months'' --search tickets where where tags not like odbc_powerpack and updated in last one month
	--,query=''type:ticket status<solved created>1months'' --search tickets with status not solved and created in last one month
	--,query=''type:ticket created:2014-08-01'' --search tickets created on 2014-08-01 (UTC)
	--,query=''type:ticket created>2014-08-01T10:30:00Z created<2014-08-01T12:00:00Z'' --search tickets created between two datetime (UTC time)
	--,query=''type:ticket requester:amy@mondocam.com'' --search tickets where requester is amy@mondocam.com
	--,query=''type:ticket description:defective'' --search tickets where decription contains word "defective"
	--,query=''type:ticket description:"product is defective"'' --search tickets where decription contains phrase  "product is defective"
	--,query=''type:ticket priority>low'' --search tickets where priority greater than low (e.g. medium or high)"
	
	--,query=''ZappySys'' --phrase match (no type): anywhere in ticket / organization / user contains ZappySys word - If filtertype parameter supplied then only those type included
	--,query=''ZappySys*'' --phrase match (no type): wildcard usage
	--,query=''type:user "ZappySys"'' --search users with term "ZappySys" anywhere in text properties
    --,query=''type:user email:"@zappysys"'' --search users with term "@ZappySys.com" in email field
	--,query=''type:user name:"zappysys"'' --search users with term "ZappySys" in name field
	--,query=''type:user details:"zappysys"'' --search users with term "ZappySys" in details field
    --,query=''type:organization "ZappySys LLC"'' --phrase match: organization name contains ZappySys LLC
)')

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_ZENDESK_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_search --limited to 1000 rows max
--OR
--FROM get_search_export --returns more than 1000 rows but no sorting or extra column allowed
WITH(
       filtertype='''' --can be ticket, organization, user, group. If you set here then no need to put in query (e.g. query=''type:ticket ...''). This option is ignored for get_search_Export.
	  ,sort_by=''updated_at'' --created_at, status, priority, ticket_type. This option is ignored for get_search_Export.
	  ,sort_order=''asc''  --desc (not valid for get_search_export). This option is ignored for get_search_Export.

    --read this for more information 
	--https://support.zendesk.com/hc/en-us/articles/4408883318554-Searching-users-groups-and-organizations
	--https://support.zendesk.com/hc/en-us/articles/4408886879258-Zendesk-Support-search-reference#topic_crj_yev_uc
	--https://support.zendesk.com/hc/en-us/articles/4408882086298-Searching-tickets
	
      ,query=''type:ticket subject:"Upsert Destination"''   --search tickets where subject contains phrase "Upsert Destination" (special characters like comma / dash etc ignored in match)
	--,query=''type:ticket 519872''   --search ticket with id 519872
	--,query=''type:ticket tags:solved,odbc_powerpack updated>1months'' --search tickets where two tags set solved,odbc_powerpack and updated in last one month
	--,query=''type:ticket -tags:odbc_powerpack updated>1months'' --search tickets where where tags not like odbc_powerpack and updated in last one month
	--,query=''type:ticket status<solved created>1months'' --search tickets with status not solved and created in last one month
	--,query=''type:ticket created:2014-08-01'' --search tickets created on 2014-08-01 (UTC)
	--,query=''type:ticket created>2014-08-01T10:30:00Z created<2014-08-01T12:00:00Z'' --search tickets created between two datetime (UTC time)
	--,query=''type:ticket requester:amy@mondocam.com'' --search tickets where requester is amy@mondocam.com
	--,query=''type:ticket description:defective'' --search tickets where decription contains word "defective"
	--,query=''type:ticket description:"product is defective"'' --search tickets where decription contains phrase  "product is defective"
	--,query=''type:ticket priority>low'' --search tickets where priority greater than low (e.g. medium or high)"
	
	--,query=''ZappySys'' --phrase match (no type): anywhere in ticket / organization / user contains ZappySys word - If filtertype parameter supplied then only those type included
	--,query=''ZappySys*'' --phrase match (no type): wildcard usage
	--,query=''type:user "ZappySys"'' --search users with term "ZappySys" anywhere in text properties
    --,query=''type:user email:"@zappysys"'' --search users with term "@ZappySys.com" in email field
	--,query=''type:user name:"zappysys"'' --search users with term "ZappySys" in name field
	--,query=''type:user details:"zappysys"'' --search users with term "ZappySys" in details field
    --,query=''type:organization "ZappySys LLC"'' --phrase match: organization name contains ZappySys LLC
)'
EXEC (@MyQuery) AT [LS_TO_ZENDESK_IN_GATEWAY]