SQL Server guide

Update or create an issue with custom fields

Updates or creates an issue including custom fields of various types: dropdown, radio, checkboxes, date, labels, number, paragraph, URL, user, team, and single-line text. Use the custom field ID (e.g. customfield_10050) and the appropriate suffix or raw JSON format as shown in the example. For insert, use the same column list in INSERT INTO Issues(...).

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.

UPDATE Issues
SET customfield_10050='[{"value":"AAA"},{"value":"BBB"}]'	--CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051='2020-12-31'	--CUSTOM Date field update
,customfield_10052='2020-12-31T23:59:59'	--CUSTOM DateTime field update

--Custom Dropdown / Radio fields
,customfield_10048_value='BBB'	--CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id='10022'	--CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048='{"value":"BBB"}'	--CUSTOM Dropdown (Using Raw value)

,customfield_10053='["bugfix","test"]'	--CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057='[{"value":"AAA"},{"value":"BBB"}]'	--CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123	--CUSTOM Number field update
,customfield_10054_text='Long string...line-1 
	Long string... line-2 
	Long string... line-3'	--CUSTOM Paragraph field update

,customfield_10055_value='BBB'	--CUSTOM Radio field update by value
--OR--
--,customfield_10055_id='10023'	--CUSTOM Radio field update

,customfield_10058='https://zappysys.com'	--CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059='{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}'	--CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060='Custom text single line'	--CUSTOM text single line field 
,customfield_10001='577069e1-1bcd-4b1e-9070-0b2475830d1c'	--CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
	IssueIdOrKey='ISSKEY', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)

--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060) 
--VALUES ('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue', '[{"value":"AAA"},{"value":"BBB"}]' ,... 'Custom text single line')

'

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_JIRA_IN_GATEWAY], 'UPDATE Issues
SET customfield_10050=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051=''2020-12-31''	--CUSTOM Date field update
,customfield_10052=''2020-12-31T23:59:59''	--CUSTOM DateTime field update

--Custom Dropdown / Radio fields
,customfield_10048_value=''BBB''	--CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id=''10022''	--CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048=''{"value":"BBB"}''	--CUSTOM Dropdown (Using Raw value)

,customfield_10053=''["bugfix","test"]''	--CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123	--CUSTOM Number field update
,customfield_10054_text=''Long string...line-1 
	Long string... line-2 
	Long string... line-3''	--CUSTOM Paragraph field update

,customfield_10055_value=''BBB''	--CUSTOM Radio field update by value
--OR--
--,customfield_10055_id=''10023''	--CUSTOM Radio field update

,customfield_10058=''https://zappysys.com''	--CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059=''{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}''	--CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060=''Custom text single line''	--CUSTOM text single line field 
,customfield_10001=''577069e1-1bcd-4b1e-9070-0b2475830d1c''	--CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
	IssueIdOrKey=''ISSKEY'', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)

--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060) 
--VALUES (''SMP'', ''Task'', ''My ticket inserted through the API'', ''A description about an issue'', ''[{"value":"AAA"},{"value":"BBB"}]'' ,... ''Custom text single line'')

''')

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) = 'UPDATE Issues
SET customfield_10050=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051=''2020-12-31''	--CUSTOM Date field update
,customfield_10052=''2020-12-31T23:59:59''	--CUSTOM DateTime field update

--Custom Dropdown / Radio fields
,customfield_10048_value=''BBB''	--CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id=''10022''	--CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048=''{"value":"BBB"}''	--CUSTOM Dropdown (Using Raw value)

,customfield_10053=''["bugfix","test"]''	--CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057=''[{"value":"AAA"},{"value":"BBB"}]''	--CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123	--CUSTOM Number field update
,customfield_10054_text=''Long string...line-1 
	Long string... line-2 
	Long string... line-3''	--CUSTOM Paragraph field update

,customfield_10055_value=''BBB''	--CUSTOM Radio field update by value
--OR--
--,customfield_10055_id=''10023''	--CUSTOM Radio field update

,customfield_10058=''https://zappysys.com''	--CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059=''{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}''	--CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060=''Custom text single line''	--CUSTOM text single line field 
,customfield_10001=''577069e1-1bcd-4b1e-9070-0b2475830d1c''	--CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
	IssueIdOrKey=''ISSKEY'', --or use Id in where clause 
	Output=1,
	NotifyUsers=0,
	OverrideScreenSecurity=0,
	OverrideEditableFlag=0,
	ContinueOn404Error=0
)

--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060) 
--VALUES (''SMP'', ''Task'', ''My ticket inserted through the API'', ''A description about an issue'', ''[{"value":"AAA"},{"value":"BBB"}]'' ,... ''Custom text single line'')

'''
EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY]