SET NOCOUNT ON
-- CREATE A NEW GROUP
INSERT Util.SE.SQLExecutionGroup
(Description, UseTransaction)
VALUES ('Test', 0)
DECLARE @ExecutionGroupId INT = SCOPE_IDENTITY()
SET CONTEXT_INFO @ExecutionGroupId
PRINT '-- @ExecutionGroupId = ' + CAST(@ExecutionGroupId AS VARCHAR) + '
SELECT * FROM Util.SE.SQLExecutionGetGroup(' + CAST(@ExecutionGroupId AS VARCHAR) + ')'
GO
DECLARE @ExecutionGroupId INT = CAST(CAST(CONTEXT_INFO() AS VARBINARY(4)) AS INT)
INSERT Util.SE.SQLExecution
(ExecutionGroupId,
Description,
DatabaseName,
CommandText,
UseTransaction)
SELECT @ExecutionGroupId AS ExecutionGroupId,
CommandText AS Description,
DB_NAME() AS DatabaseName,
CommandText,
0 AS UseTransaction
FROM Util.dbo.GetNumbers(1, 10)
CROSS APPLY (SELECT 'WAITFOR DELAY ''00:01:' + RIGHT ('0' + CAST( digit AS VARCHAR), 2) + '''' AS CommandText) b
GO
-- CHECK THE STATUS MANUALLY
SELECT *
FROM Util.SE.SQLExecutionGetGroup(CAST(CAST(CONTEXT_INFO() AS VARBINARY(4)) AS INT))
GO
-- Look at everything
SELECT *
FROM Util.SE.SQLExecutionAll
GO
-- Look at everything that's active
SELECT *
FROM Util.SE.SQLExecutionActive
GO
-- Kill all connections belong to a group
DECLARE @ExecutionGroupId INT = CAST(CAST(CONTEXT_INFO() AS VARBINARY(4)) AS INT)
EXEC Util.SE.SQLExecutionKillAll
@ExecutionGroupId = @ExecutionGroupId,
@ExecutionId = NULL,
@PrintSQL = 1,
@ExecSQL = 0
GO
-- WAIT FOR THE GROUP
DECLARE @ExecutionGroupId INT = CAST(CAST(CONTEXT_INFO() AS VARBINARY(4)) AS INT)
EXEC Util.SE.SQLExecutionWait
@ExecutionGroupId = @ExecutionGroupId
GO
-- WAIT FOR THE GROUP FORM
DECLARE @ReturnVal INT,
@ExecutionGroupId INT= CAST(CAST(CONTEXT_INFO() AS VARBINARY(4)) AS INT),
@ExecutionId INT,
@InitialCount INT,
@RunningCount INT,
@ActiveSessionCount INT,
@CommitWaitCount INT,
@ErrorCount INT,
@CompleteCount INT,
@SumRowCnt INT,
@DistRowCnt INT,
@Duration INT,
@TotalDuration INT,
@ErrorReport VARCHAR(MAX)
EXEC @ReturnVal = Util.SE.SQLExecutionWait
@ExecutionGroupId = @ExecutionGroupId,
@ExecutionId = @ExecutionId,
@InitialCount = @InitialCount OUTPUT,
@RunningCount = @RunningCount OUTPUT,
@ActiveSessionCount = @ActiveSessionCount OUTPUT,
@CommitWaitCount = @CommitWaitCount OUTPUT,
@ErrorCount = @ErrorCount OUTPUT,
@CompleteCount = @CompleteCount OUTPUT,
@SumRowCnt = @SumRowCnt OUTPUT,
@DistRowCnt = @DistRowCnt OUTPUT,
@Duration = @Duration OUTPUT,
@TotalDuration = @TotalDuration OUTPUT,
@ErrorReport = @ErrorReport OUTPUT,
@TimeOut = 7200,
@GenerateErrorOnTimeout = 1,
@SelectResults = 1,
@PrintError = 1,
@PrintStatus = 1
SELECT @ReturnVal AS '@ReturnVal',
@ExecutionGroupId AS '@ExecutionGroupId',
@ExecutionId AS '@ExecutionId',
@InitialCount AS '@InitialCount',
@RunningCount AS '@RunningCount',
@ActiveSessionCount AS '@ActiveSessionCount',
@CommitWaitCount AS '@CommitWaitCount',
@ErrorCount AS '@ErrorCount',
@CompleteCount AS '@CompleteCount',
@SumRowCnt AS '@SumRowCnt',
@DistRowCnt AS '@DistRowCnt',
@Duration AS '@Duration',
@TotalDuration AS '@TotalDuration',
@ErrorReport AS '@ErrorReport'