Read issues

Gets all issues. By default the query returns every issue; you can narrow results by adding a WITH clause to search by Key, Project, or JQL expression (see the commented options in the code).

Use SearchBy='Key' with a single key or ID for one issue, or comma-separated keys/IDs for multiple. Use SearchBy='Project' to limit to one or more project codes. Use SearchBy='Jql' with a JQL expression for advanced filtering.

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 Issues

--//Query single issue by numeric Issue Id
--SELECT * FROM Issues Where Id=101234

--//Query issue by numeric Issue Ids (multiple)
--SELECT * FROM Issues WITH(SearchBy='Key', Key='101234,101235,101236')

--//Query issue by Issue Key(s) (alpha-numeric)
--SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11')
--SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11,PROJ-12,PROJ-13')

--//Query issue by project(s)
--SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ')
--SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ,KAN,CS')

--//Query issue by JQL expression
--SELECT * FROM Issues WITH(SearchBy='Jql', Jql='status IN (Done, Closed) AND created > -5d' )

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_JIRA_IN_GATEWAY], 'SELECT * FROM Issues

--//Query single issue by numeric Issue Id
--SELECT * FROM Issues Where Id=101234

--//Query issue by numeric Issue Ids (multiple)
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''101234,101235,101236'')

--//Query issue by Issue Key(s) (alpha-numeric)
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''PROJ-11'')
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''PROJ-11,PROJ-12,PROJ-13'')

--//Query issue by project(s)
--SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''PROJ'')
--SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''PROJ,KAN,CS'')

--//Query issue by JQL expression
--SELECT * FROM Issues WITH(SearchBy=''Jql'', Jql=''status IN (Done, Closed) AND created > -5d'' )')

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) = 'SELECT * FROM Issues

--//Query single issue by numeric Issue Id
--SELECT * FROM Issues Where Id=101234

--//Query issue by numeric Issue Ids (multiple)
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''101234,101235,101236'')

--//Query issue by Issue Key(s) (alpha-numeric)
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''PROJ-11'')
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''PROJ-11,PROJ-12,PROJ-13'')

--//Query issue by project(s)
--SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''PROJ'')
--SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''PROJ,KAN,CS'')

--//Query issue by JQL expression
--SELECT * FROM Issues WITH(SearchBy=''Jql'', Jql=''status IN (Done, Closed) AND created > -5d'' )'
EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY]