Video Screencast Help

SQL Code To Clean The Tickets in ServiceDesk

Created: 20 Feb 2013 • Updated: 21 Feb 2013 | 6 comments
brunoyep's picture

 

Hi everybody

This is a SQL code to clean the tickets in ServiceDesk.

I do some tests in ServiceDesk 7.1 and the tickets were cleaned, but you won't return to the ticket IM-000001.

It's just to clean the ticket.

You just need to change the 'IM-000091' with the number of the ticket that you want to clean.

Regards,

Bruno Bacelar

Comments 6 CommentsJump to latest comment

Jorge Fernandez's picture

Hi

If you need to restart the number of the ticket to IM-000001 you need to delete this table
[ProcessManager].[dbo].[ReportProcessGenerator]

But backup the table first XD, i never clean this table, only i use this table to make the next number more high

try with this query
update [ReportProcessGenerator]
set [LastUsedIdentity] = 0
where [TYPE] = 'IM-'

atte. Jorge Fernandez

seralar's picture

HI,

 

I will test it with servicedesk 7.5....

brunoyep's picture

Yes, test it in the 7.5 and post here after.

If you need a code to clean the base and return to te IM-000001, I have a code to do that.

Bruno Bacelar

seralar's picture

 

Hi,
 
I modified the script to work with 7.5, I tested it and went fine. Here the code
 
USE ProcessManager
DECLARE @Process_ID nvarchar(50),
@Session_ID nvarchar(36),
@Task_ID nvarchar (36)
 
SET @Process_ID = 'IM-XXXXXX'  --Specific Process ID to be deleted.
SET @Session_ID = (SELECT SessionID FROM ReportProcess WHERE ReportProcessID = @Process_ID)
 
DECLARE cur_TaskID CURSOR FOR
SELECT TaskID 
FROM Task 
WHERE SessionID = @Session_ID
 
DELETE FROM ReportProcess WHERE SessionID = @Session_ID
DELETE FROM ReportProcessStatusHistory WHERE SessionID = @Session_ID
 
OPEN cur_TaskID
FETCH NEXT FROM cur_TaskID INTO @Task_ID
 
WHILE (@@FETCH_STATUS=0)
BEGIN
delete from TaskAssignment 
where TaskID = @Task_ID
FETCH NEXT FROM cur_TaskID INTO @Task_ID
End
 
DELETE FROM Task WHERE SessionID = @Session_ID
DELETE FROM [Messages] WHERE MessageID = @Session_ID
DELETE from ImIncidentTicket where SessionId = @Session_ID
 
CLOSE cur_TaskID
DEALLOCATE cur_TaskID
 
 
Any comment?

 

brunoyep's picture

This code is just for a ticket or it clean every database and return to IM-000001 ?

Bruno Bacelar

seralar's picture

nop .. is for only a ticket at time .. to clean whole database the TGiles code (SD70.Purge_.updated.txt) needs to be modified...