Read issues with all columns (including SLA / nested custom fields)

Gets issues with all columns exposed, including SLA and nested custom fields, without defining a META parameter. Set the connector’s Metadata Mode to MergeStaticDynamic so the driver scans sample data and merges static and dynamic columns. This approach is simpler but slower because of the extra requests used to infer column types from data.

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.

-- No META needed. Set MetaDetectionOrder to MergeStaticDynamic so nested/SLA columns are discovered automatically.
-- Slower: driver makes extra requests to scan data and merge with static metadata.

SELECT *
FROM Issues
WITH(
	  JQL='project IN(SUP)'
	  ,MetaDetectionOrder='MergeStaticDynamic'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_JIRA_IN_GATEWAY], '-- No META needed. Set MetaDetectionOrder to MergeStaticDynamic so nested/SLA columns are discovered automatically.
-- Slower: driver makes extra requests to scan data and merge with static metadata.

SELECT *
FROM Issues
WITH(
	  JQL=''project IN(SUP)''
	  ,MetaDetectionOrder=''MergeStaticDynamic''
)')

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

DECLARE @MyQuery NVARCHAR(MAX) = '-- No META needed. Set MetaDetectionOrder to MergeStaticDynamic so nested/SLA columns are discovered automatically.
-- Slower: driver makes extra requests to scan data and merge with static metadata.

SELECT *
FROM Issues
WITH(
	  JQL=''project IN(SUP)''
	  ,MetaDetectionOrder=''MergeStaticDynamic''
)'
EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY]