Table WorkItems
Description
No description available
Parameters
Parameter | Label | Required | Options | Description | Help |
---|---|---|---|---|---|
Query | Wiql Query | YES |
|
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

Read/write to WorkItems table using API Destination

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 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
')
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')
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'
, [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
Add Tags to an exising workitem.
UPDATE WorkItems
SET Tags = 'newtag1; newtag2'
WHERE Id = 6455
WITH(
Project='ProductTesting'
,Operation='add'
)
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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];
Get work items for the specific project.
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WITH (Project=''ProductTesting'')';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];
Get a specific work item by its Id.
DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM WorkItems WHERE Id=1001';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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'')';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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''
, [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';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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''
)';
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 WorkItems WHERE Id = 7455';
EXEC (@MyQuery) AT [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_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 [LINKED_SERVER_TO_AZURE_DEVOPS_IN_DATA_GATEWAY];