Table WorkItemComments

Parameters

Supported Operations

Examples

SSIS

Use Azure DevOps Connector in API Source component to read data or in API Destination component to read/write data:

Read from WorkItemComments table using API Source

Azure DevOps

WorkItemComments

Read/write to WorkItemComments table using API Destination

Azure DevOps

WorkItemComments

Select

ODBC application

Use these SQL queries in your ODBC application data source:

Get all comments for all work items

SELECT * FROM WorkItemComments

Get all comments for a work item

SELECT * FROM WorkItemComments 
WHERE WorkItemId=6455

Get a comment by Id for a work item

SELECT * FROM WorkItemComments 
WHERE WorkItemId=6455 and Id=5283490

Query work item comments using Wiql.

SELECT 
	  Id
	, WorkItemId
	, Text
	, RenderedText
	, Format
	, CreatedDate
	, CreatedByUniqueName
	, CreatedById
	, CreatedByDisplayName
	, ModifiedDate
	, ModifiedByUniqueName
	, ModifiedById
	, ModifiedByDisplayName
	, Url
FROM WorkItemComments 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query='SELECT [System.Id] FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')

Create work item comment

INSERT INTO WorkItemComments(WorkItemId, Text)
VALUES(6455, 'Adding <strong>html comment</strong> - created on <<FUN_NOW>>')
WITH(Project='ProductTesting')

Update work item comment

UPDATE WorkItemComments
SET Text='Updating <strong>html comment</strong> - updated on <<FUN_NOW>>'
Where WorkItemId=6455 and Id=5284411
WITH(Project='ProductTesting')

Delete a specific work item by its Id.

DELETE FROM WorkItemComments WHERE WorkItemId=6455 and Id=5284411

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get all comments for all work items

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItemComments';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];

Get all comments for a work item

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItemComments 
WHERE WorkItemId=6455';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];

Get a comment by Id for a work item

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItemComments 
WHERE WorkItemId=6455 and Id=5283490';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];

Query work item comments using Wiql.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  Id
	, WorkItemId
	, Text
	, RenderedText
	, Format
	, CreatedDate
	, CreatedByUniqueName
	, CreatedById
	, CreatedByDisplayName
	, ModifiedDate
	, ModifiedByUniqueName
	, ModifiedById
	, ModifiedByDisplayName
	, Url
FROM WorkItemComments 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query=''SELECT [System.Id] FROM WorkItems WHERE [System.TeamProject]=''''ProductTesting'''' and [System.Id]=6455 ORDER BY [System.Id] DESC'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];

Create work item comment

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItemComments(WorkItemId, Text)
VALUES(6455, ''Adding <strong>html comment</strong> - created on <<FUN_NOW>>'')
WITH(Project=''ProductTesting'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];

Update work item comment

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItemComments
SET Text=''Updating <strong>html comment</strong> - updated on <<FUN_NOW>>''
Where WorkItemId=6455 and Id=5284411
WITH(Project=''ProductTesting'')';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];

Delete a specific work item by its Id.

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM WorkItemComments WHERE WorkItemId=6455 and Id=5284411';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];