Reference

Table WorkItems

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT query_workitems
INSERT add_workitem
UPDATE update_workitem
UPSERT
DELETE delete_workitem
LOOKUP get_workitems_by_ids

Examples

SSIS

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

Read from WorkItems table using API Source

API Source - Azure DevOps
Azure DevOps Connector can be used to integrate Azure DevOps data in your App / BI Tools. You can create, read, query, modify, and delete WorkItems, Projects, Teams and more.
Azure DevOps
WorkItems
Required Parameters
Project Name Fill-in the parameter...
Wiql Query Fill-in the parameter...
Optional Parameters
Team Id or Name
Use time precision
Expand Parameters (Set Blank for fast response) Relations
SSIS API Source - Read from table or endpoint

Read/write to WorkItems table using API Destination

API Destination - Azure DevOps
Azure DevOps Connector can be used to integrate Azure DevOps data in your App / BI Tools. You can create, read, query, modify, and delete WorkItems, Projects, Teams and more.
Azure DevOps
WorkItems
Select
Required Parameters
Project Name Fill-in the parameter...
Wiql Query Fill-in the parameter...
Optional Parameters
Team Id or Name
Use time precision
Expand Parameters (Set Blank for fast response) Relations
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get work items for the default project(specified by connection settings) 

SELECT * FROM WorkItems

Get work items for the specific project 

SELECT * FROM WorkItems WITH (Project='ProductTesting')

Get work items with ParentId

By default some fields like ParentId is not returned to speed up, if you wish to populate that field then set Expand='Relations' 

SELECT * FROM WorkItems WITH(Expand='Relations')

Get a specific work item by its Id 

SELECT * FROM WorkItems WHERE Id=1001

Get a specific work item by its Id, but halt the process if a 404 error is encountered 

SELECT * FROM WorkItems WHERE Id='8563' WITH (ContineOn404Error='False')

Create a query to get work items by the Id of the work items in the default project for the organization

To learn more about Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops 

SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems 
WHERE [System.Id] > 2000 AND [System.Id] < 7050 
	AND [System.TeamProject]=''ProductTesting''
	AND [System.ChangedDate] < @Today
	AND [System.CreatedDate] > @StartOfYear
	AND [System.WorkItemType]=''Task'' 
	AND [System.State]=''Resolved'' 
	AND [Microsoft.VSTS.Common.Priority] >= 1
	ORDER BY [System.ChangedDate] DESC
	')

Get WorkItems modified after a date (dynamic)

You can use placeholder functions to make date search dynamic, you can use now, today, yesterday, monthstart, monthend, yearstart, yearend and more. Also add or subtract Days, Months, Years, Hours using + or - syntax in expression (e.g. monthstart-1d subtracts 1 day from month start date). Visit this link to learn more about Placeholder function: https://zappysys.com/links/?id=10014. To learn more about WIQL Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops 

SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems 
WHERE [System.TeamProject]=''ProductTesting''
	AND [System.ChangedDate] >= ''<<monthstart-1d,FUN_TO_DATE>>'' 
ORDER BY [System.ChangedDate] DESC
	')

Create a query to get work items by the Id of the work items in the specified project for the organization 

SELECT * FROM WorkItems WITH (Project='PosProject', Query='SELECT * FROM WorkItems WHERE [System.Id] = 2819')

Create a query to get work items by the types of work items in the default project for the organization 

SELECT * FROM WorkItems WITH (Query='SELECT * FROM WorkItems WHERE [System.WorkItemType] = ''Task''')

Create a new work item in the default project for the organization 

INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES ('task', 'Fix Issues in System', 'My Custom Text', '2021-01-01T23:59:59.123', 1000)

Create a new work item in a specific project 

INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES ('task', 'Fix Issues in System', 'My Custom Text', '2021-01-01T23:59:59.123', 1000)
WITH(Project='MyProject')

Create a new work with many fields in a specific project 

INSERT INTO WorkItems  (   
      [WorkItemType]   
    , [Title] 
	, [Description] 
	, [Tags]
	, [AreaPath]  
	, [IterationPath] 
	, [AssignedTo] 
	, [Priority]
    , [Triage] 	
  --, [StackRank]  
	, [Blocked]  
	, [TaskType] 
	, [RequiresReview]
	, [RequiresTest] 
	, [IntegrationBuild] 
	, [FoundIn] 
	, [OriginalEstimate] 
	, [RemainingWork] 
	, [CompletedWork] 
	, [Size] 
	, [Effort] 
	, [TargetDate] 
	, [StartDate] 
	, [FinishDate] 
	, [DueDate] 
	, [StoryPoints] 
  --, [Discipline] 
	, [SystemInfo] 
	, [Steps] 
	, [ReproSteps] 
	--, [CustomFieldText]  --custom field prefix with CustomYOUR_FIELD_NAME_WITHOUT_SPACE_OR_DASH_OR_DOT
	--, [CustomFieldDate] 
	--, [CustomFieldNumber] 
	--, [CustomFieldDecimal] 
	--, [CustomFieldPickListString] 
	--, [CustomFieldPickListNum] 
)
VALUES (
      'Bug' -- Task, User Story -- WorkItemType
    , 'My Task Title - Created on <<FUN_NOW>>'  --Title
    , 'Some Description - Created on <<FUN_NOW>>' --Description
    , 'odata; api' --Tags
    , 'ProductTesting\SSISPowerPack' --AreaPath
    , 'ProductTesting\2021.6' --IterationPath
    , 'build@mycompany.com' --AssignedTo
    , 1 --Priority
    , 'Pending' --Triage
  --, ??? --[StackRank]
	, 'No' --Blocked
	, 'Planned' --TaskType
	, 'No' --RequiresReview
	, 'No' --RequiresTest
	, '101.1.2.3' --IntegrationBuild
	, '100.1.2.3' --FoundIn
	, 9999.111 --OriginalEstimate
	, 9999.222 --RemainingWork
	, 9999.333 --CompletedWork
	, 9999.444 --Size
	, 9999.555 --Effort
	, '2021-01-31' --TargetDate
	, '2022-01-31' --StartDate
	, '2023-01-31' --FinishDate
	, '2024-01-31' --DueDate
	, 100.5 --StoryPoints
  --, 'Some Discipline' --Discipline
	, 'Windows 10' --SystemInfo
	, 'Here are steps' --Steps
	, 'Here are steps to reproduce' --ReproSteps
	--, 'abcd'  --CustomFieldText
	--, '2021-01-31' --CustomFieldDate
	--, 9999  --CustomFieldNumber 
	--, 9999.123 --CustomFieldDecimal
	--, 'BBB' --CustomFieldPickListString
	--, '2' --CustomFieldPickListNum
)
WITH(
	Project='ProductTesting'
-- ,BypassRules='true'  --Useful to set ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications='true'  --Avoids email notifications on change
-- ,ValidateOnly='true'  --Dont perform actual insert - just validate
)

Update a specific work item by its Id 

UPDATE WorkItems SET
Title = 'Renamed Work Item Title',
CustomMyField = 'New Custom Text'
WHERE Id = 7455

Update a work item by its Id (many columns) 

UPDATE WorkItems
SET   [Title] = 'Update-QA Task <<FUN_NOW>>'
	, [Description] = 'Updated desc <<FUN_NOW>>'
  --, [WorkItemType]='Bug' -- Task
	, [State] = 'Active'
	, [Tags] = 'odata; api'
	, [Revision] = 1
	, [AreaPath] = 'ProductTesting\SSISPowerPack'
	, [TeamProject] = 'ProductTesting'
	, [IterationPath] = 'ProductTesting\2021.6'
	, [Reason] = 'Reactivated'
	, [AssignedTo] = 'build@mycompany.com'
	, [Priority] = 1
  --, [Triage] = 'Pending'	
  --, [StackRank] = ???
	, [Blocked] = 'No'
	, [TaskType] = 'Planned'
	, [RequiresReview] = 'No'
	, [RequiresTest] = 'No'
	, [ActivatedDate] = '2021-01-31'
  --, [ResolvedReason] = 'Complete and Requires Review/Test'
  --, [ResolvedBy] = 'John Smith <jsmith@mycompany.com>' --or Just Name or Email (Must set ByPassRules='true' Param to set this readonly field)
	, [IntegrationBuild] = '101.1.2.3'
	, [FoundIn] = '100.1.2.3'
	, [OriginalEstimate] = 9999.123
	, [RemainingWork] = 9999.123
	, [CompletedWork] = 9999.123
	, [Size] = 9999.123
	, [Effort] = 9999.123
	, [TargetDate] = '2021-01-31'
	, [StartDate] = '2021-01-31'
	, [FinishDate] = '2021-01-31'
	, [DueDate] = '2021-01-31'
	, [StoryPoints] = 100.5
--	, [Discipline] = 'abcd'
	, [SystemInfo] = 'abcd'
	, [Steps] = 'abcd'
	, [ReproSteps] = 'abcd'
--	, [CustomFieldText] = 'abcd' 
--	, [CustomFieldDate] = '2021-01-31' 
--	, [CustomFieldNumber] = 9999  
--	, [CustomFieldDecimal] = 9999.123 
--	, [CustomFieldPickListString] = 'BBB' 
--	, [CustomFieldPickListNum] = '2' 
WHERE [Id] = 6455
--WITH(
--  BypassRules='true'  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications='true'  --Avoids email notifications on change
-- ,ValidateOnly='true'  --Dont perform actual update - just validate
--)

Add Tags to an exising workitem 

UPDATE WorkItems
SET   Tags = 'newtag1; newtag2'
WHERE Id = 6455
WITH(
	   Project='ProductTesting'
	  ,Operation='add'
   -- ,BypassRules='true'  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
   -- ,SuppressNotifications='true'  --Avoids email notifications on change
   -- ,ValidateOnly='true'  --Dont perform actual update - just validate	  
)

Update readonly fields (e.g. ClosedBy, ResolvedBy) 

UPDATE WorkItems
SET   [Title] = 'Update-QA Task <<FUN_NOW>>'
	, [Description] = 'Updated desc <<FUN_NOW>>'
    , [WorkItemType]='Bug' -- Task
	, [State] = 'Resolved'
	, [Tags] = 'odata; api'
	, [ResolvedBy] = 'some.one@mycompany.com' --(Must set ByPassRules='true' Param to set this readonly field)
WHERE [Id] = 9626
WITH(
  BypassRules='true'  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications='true'  --Avoids email notifications on change
-- ,ValidateOnly='true'  --Dont perform actual update - just validate
)

Delete a specific work item by its Id 

DELETE FROM WorkItems WHERE Id = 7455

Delete work items match with Wiql Query 

DELETE FROM WorkItems
Where 1=1 
WITH(Query='SELECT * FROM WorkItems Where [System.TeamProject]=''MyProject'' AND ([System.Id]=6469 OR [System.Id]=6468) ')

Update work items match with Wiql Query 

UPDATE WorkItems
Set Title='New Title - Updated on <<FUN_NOW>>'
Where 1=1 
WITH(Query='SELECT * FROM WorkItems Where [System.TeamProject]=''MyProject'' AND ([System.Id]=6469 OR [System.Id]=6468) ')

Query work items using Wiql

This example shows how to use WIQL language to query Azure DevOps WorkItems. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops 

SELECT 
	  Id
	, Title
	, Description
	, CreatedDate
	, Url
FROM WorkItems 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query='SELECT * FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')

Query work items using Wiql (with #DirectSQL Tag)

This example shows how to use WIQL language to query Azure DevOps WorkItems using #DirectSQL pre processor to invoke server side query engine. This one is same as previous example except simplified SQL. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops 

#DirectSQL SELECT * FROM WorkItems WHERE [System.TeamProject]=''ProductTesting'' and [System.Id]=6455 ORDER BY [System.Id] DESC')

Query Sprint Hours by Team Member - Esimated vs Actual Completed

This query shows how to query total time spent by team members during a specific Sprint (i.e. Iteration Path). This query only works if your team is updating Original Estimate and Completed Hours on work item after they are done with the task. 

SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project='ProductTesting'
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\Sprint 1'' and State IN(''Resolved'', ''Closed'')'
	  
	  --Use below to query all sprints for this Product
	  --,Query='SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''ProductTesting\'' and State IN(''Resolved'', ''Closed'')'
)

Read more than 20000 work items (UNION ALL)

By default Azure DevOps API returns max 20000 items. If you want more than 20000 items then you can try below technique. Run enire query at once. Basically it creates many temp table each consisting 20000 rows each and then in final query we do UNION ALL 

SELECT * INTO #t1 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=1 and [System.Id]<20000');
SELECT * INTO #t2 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=20000 and [System.Id]<40000');
SELECT * INTO #t3 FROM WorkItems WITH(Project='ProductTesting', Query='SELECT * FROM WorkItems WHERE [System.Id]>=40000 and [System.Id]<60000');
--//....
--//add more temp tables above and update UNION ALL too

SELECT * FROM #t1 
UNION ALL 
SELECT * FROM #t2
UNION ALL 
SELECT * FROM #t3
;

SQL Server

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

Get work items for the default project(specified by connection settings) 

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

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get work items for the specific project 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''ProductTesting'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get work items with ParentId

By default some fields like ParentId is not returned to speed up, if you wish to populate that field then set Expand='Relations' 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH(Expand=''Relations'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get a specific work item by its Id 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=1001';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get a specific work item by its Id, but halt the process if a 404 error is encountered 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=''8563'' WITH (ContineOn404Error=''False'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a query to get work items by the Id of the work items in the default project for the organization

To learn more about Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems 
WHERE [System.Id] > 2000 AND [System.Id] < 7050 
	AND [System.TeamProject]=''''ProductTesting''''
	AND [System.ChangedDate] < @Today
	AND [System.CreatedDate] > @StartOfYear
	AND [System.WorkItemType]=''''Task'''' 
	AND [System.State]=''''Resolved'''' 
	AND [Microsoft.VSTS.Common.Priority] >= 1
	ORDER BY [System.ChangedDate] DESC
	'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Get WorkItems modified after a date (dynamic)

You can use placeholder functions to make date search dynamic, you can use now, today, yesterday, monthstart, monthend, yearstart, yearend and more. Also add or subtract Days, Months, Years, Hours using + or - syntax in expression (e.g. monthstart-1d subtracts 1 day from month start date). Visit this link to learn more about Placeholder function: https://zappysys.com/links/?id=10014. To learn more about WIQL Query syntax refer to this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops and this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/query-operators-variables?view=azure-devops 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems 
WHERE [System.TeamProject]=''''ProductTesting''''
	AND [System.ChangedDate] >= ''''<<monthstart-1d,FUN_TO_DATE>>'''' 
ORDER BY [System.ChangedDate] DESC
	'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a query to get work items by the Id of the work items in the specified project for the organization 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''PosProject'', Query=''SELECT * FROM WorkItems WHERE [System.Id] = 2819'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a query to get work items by the types of work items in the default project for the organization 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Query=''SELECT * FROM WorkItems WHERE [System.WorkItemType] = ''''Task'''''')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a new work item in the default project for the organization 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES (''task'', ''Fix Issues in System'', ''My Custom Text'', ''2021-01-01T23:59:59.123'', 1000)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a new work item in a specific project 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItems (WorkItemType, Title, [CustomFieldText], [CustomFieldDate], [CustomFieldNumber])
VALUES (''task'', ''Fix Issues in System'', ''My Custom Text'', ''2021-01-01T23:59:59.123'', 1000)
WITH(Project=''MyProject'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Create a new work with many fields in a specific project 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO WorkItems  (   
      [WorkItemType]   
    , [Title] 
	, [Description] 
	, [Tags]
	, [AreaPath]  
	, [IterationPath] 
	, [AssignedTo] 
	, [Priority]
    , [Triage] 	
  --, [StackRank]  
	, [Blocked]  
	, [TaskType] 
	, [RequiresReview]
	, [RequiresTest] 
	, [IntegrationBuild] 
	, [FoundIn] 
	, [OriginalEstimate] 
	, [RemainingWork] 
	, [CompletedWork] 
	, [Size] 
	, [Effort] 
	, [TargetDate] 
	, [StartDate] 
	, [FinishDate] 
	, [DueDate] 
	, [StoryPoints] 
  --, [Discipline] 
	, [SystemInfo] 
	, [Steps] 
	, [ReproSteps] 
	--, [CustomFieldText]  --custom field prefix with CustomYOUR_FIELD_NAME_WITHOUT_SPACE_OR_DASH_OR_DOT
	--, [CustomFieldDate] 
	--, [CustomFieldNumber] 
	--, [CustomFieldDecimal] 
	--, [CustomFieldPickListString] 
	--, [CustomFieldPickListNum] 
)
VALUES (
      ''Bug'' -- Task, User Story -- WorkItemType
    , ''My Task Title - Created on <<FUN_NOW>>''  --Title
    , ''Some Description - Created on <<FUN_NOW>>'' --Description
    , ''odata; api'' --Tags
    , ''ProductTesting\SSISPowerPack'' --AreaPath
    , ''ProductTesting\2021.6'' --IterationPath
    , ''build@mycompany.com'' --AssignedTo
    , 1 --Priority
    , ''Pending'' --Triage
  --, ??? --[StackRank]
	, ''No'' --Blocked
	, ''Planned'' --TaskType
	, ''No'' --RequiresReview
	, ''No'' --RequiresTest
	, ''101.1.2.3'' --IntegrationBuild
	, ''100.1.2.3'' --FoundIn
	, 9999.111 --OriginalEstimate
	, 9999.222 --RemainingWork
	, 9999.333 --CompletedWork
	, 9999.444 --Size
	, 9999.555 --Effort
	, ''2021-01-31'' --TargetDate
	, ''2022-01-31'' --StartDate
	, ''2023-01-31'' --FinishDate
	, ''2024-01-31'' --DueDate
	, 100.5 --StoryPoints
  --, ''Some Discipline'' --Discipline
	, ''Windows 10'' --SystemInfo
	, ''Here are steps'' --Steps
	, ''Here are steps to reproduce'' --ReproSteps
	--, ''abcd''  --CustomFieldText
	--, ''2021-01-31'' --CustomFieldDate
	--, 9999  --CustomFieldNumber 
	--, 9999.123 --CustomFieldDecimal
	--, ''BBB'' --CustomFieldPickListString
	--, ''2'' --CustomFieldPickListNum
)
WITH(
	Project=''ProductTesting''
-- ,BypassRules=''true''  --Useful to set ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications=''true''  --Avoids email notifications on change
-- ,ValidateOnly=''true''  --Dont perform actual insert - just validate
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update a specific work item by its Id 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems SET
Title = ''Renamed Work Item Title'',
CustomMyField = ''New Custom Text''
WHERE Id = 7455';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update a work item by its Id (many columns) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
SET   [Title] = ''Update-QA Task <<FUN_NOW>>''
	, [Description] = ''Updated desc <<FUN_NOW>>''
  --, [WorkItemType]=''Bug'' -- Task
	, [State] = ''Active''
	, [Tags] = ''odata; api''
	, [Revision] = 1
	, [AreaPath] = ''ProductTesting\SSISPowerPack''
	, [TeamProject] = ''ProductTesting''
	, [IterationPath] = ''ProductTesting\2021.6''
	, [Reason] = ''Reactivated''
	, [AssignedTo] = ''build@mycompany.com''
	, [Priority] = 1
  --, [Triage] = ''Pending''	
  --, [StackRank] = ???
	, [Blocked] = ''No''
	, [TaskType] = ''Planned''
	, [RequiresReview] = ''No''
	, [RequiresTest] = ''No''
	, [ActivatedDate] = ''2021-01-31''
  --, [ResolvedReason] = ''Complete and Requires Review/Test''
  --, [ResolvedBy] = ''John Smith <jsmith@mycompany.com>'' --or Just Name or Email (Must set ByPassRules=''true'' Param to set this readonly field)
	, [IntegrationBuild] = ''101.1.2.3''
	, [FoundIn] = ''100.1.2.3''
	, [OriginalEstimate] = 9999.123
	, [RemainingWork] = 9999.123
	, [CompletedWork] = 9999.123
	, [Size] = 9999.123
	, [Effort] = 9999.123
	, [TargetDate] = ''2021-01-31''
	, [StartDate] = ''2021-01-31''
	, [FinishDate] = ''2021-01-31''
	, [DueDate] = ''2021-01-31''
	, [StoryPoints] = 100.5
--	, [Discipline] = ''abcd''
	, [SystemInfo] = ''abcd''
	, [Steps] = ''abcd''
	, [ReproSteps] = ''abcd''
--	, [CustomFieldText] = ''abcd'' 
--	, [CustomFieldDate] = ''2021-01-31'' 
--	, [CustomFieldNumber] = 9999  
--	, [CustomFieldDecimal] = 9999.123 
--	, [CustomFieldPickListString] = ''BBB'' 
--	, [CustomFieldPickListNum] = ''2'' 
WHERE [Id] = 6455
--WITH(
--  BypassRules=''true''  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications=''true''  --Avoids email notifications on change
-- ,ValidateOnly=''true''  --Dont perform actual update - just validate
--)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Add Tags to an exising workitem 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
SET   Tags = ''newtag1; newtag2''
WHERE Id = 6455
WITH(
	   Project=''ProductTesting''
	  ,Operation=''add''
   -- ,BypassRules=''true''  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
   -- ,SuppressNotifications=''true''  --Avoids email notifications on change
   -- ,ValidateOnly=''true''  --Dont perform actual update - just validate	  
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update readonly fields (e.g. ClosedBy, ResolvedBy) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
SET   [Title] = ''Update-QA Task <<FUN_NOW>>''
	, [Description] = ''Updated desc <<FUN_NOW>>''
    , [WorkItemType]=''Bug'' -- Task
	, [State] = ''Resolved''
	, [Tags] = ''odata; api''
	, [ResolvedBy] = ''some.one@mycompany.com'' --(Must set ByPassRules=''true'' Param to set this readonly field)
WHERE [Id] = 9626
WITH(
  BypassRules=''true''  --Useful to Update ReadOnly Fields like ResolvedBy, ClosedBy
-- ,SuppressNotifications=''true''  --Avoids email notifications on change
-- ,ValidateOnly=''true''  --Dont perform actual update - just validate
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Delete a specific work item by its Id 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM WorkItems WHERE Id = 7455';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Delete work items match with Wiql Query 

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM WorkItems
Where 1=1 
WITH(Query=''SELECT * FROM WorkItems Where [System.TeamProject]=''''MyProject'''' AND ([System.Id]=6469 OR [System.Id]=6468) '')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Update work items match with Wiql Query 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE WorkItems
Set Title=''New Title - Updated on <<FUN_NOW>>''
Where 1=1 
WITH(Query=''SELECT * FROM WorkItems Where [System.TeamProject]=''''MyProject'''' AND ([System.Id]=6469 OR [System.Id]=6468) '')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items using Wiql

This example shows how to use WIQL language to query Azure DevOps WorkItems. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  Id
	, Title
	, Description
	, CreatedDate
	, Url
FROM WorkItems 
--WHERE Id=5283490 -- get just one comment for specific WorkItem Id
WITH(Query=''SELECT * FROM WorkItems WHERE [System.TeamProject]=''''ProductTesting'''' and [System.Id]=6455 ORDER BY [System.Id] DESC'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query work items using Wiql (with #DirectSQL Tag)

This example shows how to use WIQL language to query Azure DevOps WorkItems using #DirectSQL pre processor to invoke server side query engine. This one is same as previous example except simplified SQL. To learn more about WIQL Syntax you can refer this link https://learn.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops 

DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL SELECT * FROM WorkItems WHERE [System.TeamProject]=''''ProductTesting'''' and [System.Id]=6455 ORDER BY [System.Id] DESC'')';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Query Sprint Hours by Team Member - Esimated vs Actual Completed

This query shows how to query total time spent by team members during a specific Sprint (i.e. Iteration Path). This query only works if your team is updating Original Estimate and Completed Hours on work item after they are done with the task. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT 
	  IterationPath as Sprint
	, AssignedToUniqueName as AssignedTo
	, SUM(OriginalEstimate) as OriginalHours_Total
	, SUM(RemainingWork) as RemainingWork_Total
	, SUM(CompletedWork) as CompletedHours_Total
FROM WorkItems
GROUP BY IterationPath,AssignedToUniqueName
WITH(
	  Project=''ProductTesting''
	  
	  --//On Preview UI Change [Max Rows] to use more sample rows - else it will use only 100 rows
	  ,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\Sprint 1'''' and State IN(''''Resolved'''', ''''Closed'''')''
	  
	  --Use below to query all sprints for this Product
	  --,Query=''SELECT * FROM WorkItems WHERE [System.IterationPath] UNDER ''''ProductTesting\'''' and State IN(''''Resolved'''', ''''Closed'''')''
)';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];

Read more than 20000 work items (UNION ALL)

By default Azure DevOps API returns max 20000 items. If you want more than 20000 items then you can try below technique. Run enire query at once. Basically it creates many temp table each consisting 20000 rows each and then in final query we do UNION ALL 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * INTO #t1 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=1 and [System.Id]<20000'');
SELECT * INTO #t2 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=20000 and [System.Id]<40000'');
SELECT * INTO #t3 FROM WorkItems WITH(Project=''ProductTesting'', Query=''SELECT * FROM WorkItems WHERE [System.Id]>=40000 and [System.Id]<60000'');
--//....
--//add more temp tables above and update UNION ALL too

SELECT * FROM #t1 
UNION ALL 
SELECT * FROM #t2
UNION ALL 
SELECT * FROM #t3
;';

EXEC (@MyQuery) AT [LS_TO_AZURE_DEVOPS_IN_GATEWAY];