The final code is:
USE ProcessManager
SET NOCOUNT ON
DECLARE @SessionID NVARCHAR(36)
DECLARE @TaskID NVARCHAR(36)
DECLARE curSDPurge CURSOR FOR
SELECT Sessionid
FROM ReportProcess
WHERE ProjectName LIKE '%SD.%'
DECLARE curTaskPurge CURSOR FOR
SELECT TaskID
FROM Task
WHERE UrlofProcess LIKE '%SD.%'
OR [Name] IN ('Reopen Incident', 'Postponement')
OPEN curSDPurge
FETCH NEXT FROM curSDPurge INTO @SessionID
WHILE (@@FETCH_STATUS=0)
BEGIN
DELETE FROM ReportProcess
WHERE SessionID = @SessionID
DELETE FROM ReportProcessComment
WHERE SessionID = @SessionID
DELETE FROM ReportProcessContact
WHERE SessionID = @SessionID
DELETE FROM ReportProcessHistory
WHERE SessionID = @SessionID
DELETE FROM ReportProcessPermission
WHERE SessionID = @SessionID
DELETE FROM ReportProcessReference
WHERE SessionID = @SessionID
DELETE FROM ReportProcessStatusHistory
WHERE SessionID = @SessionID
DELETE FROM ReportProcessTiming
WHERE SessionID = @SessionID
FETCH NEXT FROM curSDPurge INTO @SessionID
END
OPEN curTaskPurge
FETCH NEXT FROM curTaskPurge INTO @TaskID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DELETE FROM TaskAssignment
WHERE TaskID = @TaskID
DELETE FROM TaskHistory
WHERE TaskHistoryID = @TaskID
DELETE FROM TaskResponse
WHERE TaskID = @TaskID
DELETE FROM Task
WHERE TaskID = @TaskID
FETCH NEXT FROM curTaskPurge INTO @TaskID
END
DELETE FROM ReportProcessRelationship
WHERE ParentProcessID LIKE 'IM-%'
OR ChildProcessID LIKE 'IM-%'
DELETE FROM ServiceDeskIncidentManagement
DELETE FROM ServiceDeskIncidentTemplate
DELETE FROM ServiceDeskOverAllSLASchedules
DELETE FROM ServiceDeskSLA
DELETE FROM ServiceDeskSLALevelManagement
DELETE FROM ServiceDeskSurveyData
DELETE FROM ReportProcessGenerator
WHERE [Type] LIKE '%M-%'
OR [Type] = 'SURVEY-'
DELETE FROM [Messages]
WHERE QueueName LIKE '%incident%'
CLOSE curSDPurge
DEALLOCATE curSDPurge
CLOSE curTaskPurge
DEALLOCATE curTaskPurge
With this code, is possible to clean all tickets and return to the ticket IM-000001
Regards,
Bruno Bacelar