Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
nathangg

Member
Registered:
Posts: 24
Reply with quote  #1 
Is there a stored procedure I could use that I could pass it a folder ID and it would take care of all the deleting for me?

I ask because I ran an import using eRaiseFlag but it had some bad data in the import.  So, I now have folders in my table with good data AND bad data (Number of Folders with bad data = 2,300).

I want to get rid of these 2,300 folders but I'm not sure of the best way to do it (I really don't want to use the Services Manager and delete them individually :-)  ).

I'm proficient at writing SQL queries and I know I could run UPDATE statements against a bunch of tables to change the estagename and earchived fields (effectively "deleting" them in my map)... I was just nervous about creating data anomalies by UPDATING the eFolder table this way.

I also wouldn't mind deleting them with DELETE statements... but it seems I'd have to touch efolder, Five_Star_Surveys (my map table), eAlert, and eEvent... but writing delete statements against the database seems dangerous.

I'd be more comfortable if there was a stored procedure out there I could use to delete my folders.

Thanks!

0
mohnshine

Avatar / Picture

Senior Veteran
Registered:
Posts: 270
Reply with quote  #2 
The stored procedure you are looking for is ew_delete_folder

It takes the folderid as a parameter

__________________
Your best practice does not conform with my best practice.
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #3 
Not sure if I've come across that sp. I don't thing it is a Metastorm one.

We use the following, but there may be additional tables to delete from:

Quote:
CREATE procedure upDeleteFolder
(
    @FolderId VARCHAR(31)
)
as
    DECLARE @TableName varchar(31)
begin
    SET @TableName = (SELECT eMap.eTableName FROM eMap,eFolder WHERE eMap.eMapName=eFolder.eMapName AND eFolder.eFolderId=@FolderId)
    EXECUTE( 'DELETE FROM ' + @TableName + ' WHERE eFolderId=''' + @FolderId + '''' )
    DELETE FROM eEvent WHERE eFolderID = @FolderId
    DELETE FROM eWait WHERE eFolderID = @FolderId
    DELETE FROM eRaisedFlag WHERE eFolderID = @FolderId
    DELETE FROM eAlert WHERE eFolderID = @FolderId
    DELETE FROM eFolder WHERE eFolderId = @FolderId
    DELETE FROM eWait WHERE eFolderID = @FolderId
    DELETE FROM eAttachment WHERE eKey LIKE '0<tab>@FolderId%'
    UPDATE eFolder SET eParent='' WHERE eParent = @FolderId
end
GO
you would have to replace the <tab> with an actual tab - it gets changed in the post

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
BMellert

Guru
Registered:
Posts: 688
Reply with quote  #4 
Quote:

Quote:
DELETE FROM eAttachment WHERE eKey LIKE '0<tab>@FolderId%'

you would have to replace the <tab> with an actual tab - it gets changed in the post


Which can also be handled as
 ... LIKE '0'+CHAR(9)+@FolderID%' 

in SQL, or CHR(9) if Oracle, so the tab doesn't disappear if copy/paste.  (I've had that happen into some editors, so thought I'd offer this suggestion.)


0
mohnshine

Avatar / Picture

Senior Veteran
Registered:
Posts: 270
Reply with quote  #5 
Quote:
Not sure if I've come across that sp. I don't thing it is a Metastorm one.


Ah yes, if I could only follow naming conventions (since there is no else to blame)

Here is ew_delete_folder.  Essentially, it is the same as Jerome's, except that it handles the eAlert table based on the DeletionAlert setting.

 

CREATE PROCEDURE [dbo].[ew_delete_folder]
@passedfolderid NVARCHAR(31)
AS
DECLARE @folderid varchar(31)
SET @folderid = @passedfolderid

DECLARE @cmd NVARCHAR(1000)
DECLARE @mapname NVARCHAR (31)
DECLARE @tablename NVARCHAR (250)
SET @mapname = (SELECT eMapName FROM eFolder WHERE eFolderID = @folderid)
SET @tablename = (SELECT eTableName FROM eMap WHERE eMapName = @mapname)

SET @cmd = 'DELETE FROM ' + @tablename + ' WHERE EFOLDERID = ''' + @folderid + ''''
EXEC(@cmd)

DELETE FROM eFolder WHERE eFolderID = @folderid
DELETE FROM eAttachment WHERE eKey LIKE '%' + CHAR(9) + @folderid + CHAR(9) + '%'
DELETE FROM eWait WHERE eFolderID = @folderid
DELETE FROM eAssignment WHERE eFolderID = @folderid
DELETE FROM eEvent WHERE eFolderID = @folderid
DELETE FROM eAlertRequest WHERE eFolderID = @folderid
DELETE FROM eAlertGeneratorLock WHERE eFolderID = @folderid
DELETE FROM eLog WHERE eFolderID = @folderid
DELETE FROM eRaisedFlag WHERE eFlagFolder = @folderid

-- Update or remove eAlert table entries for deletion depending on the eServer.eDeletionAlerts setting.

DECLARE @l_DeletionAlert INT

SELECT @l_DeletionAlert = eDeleteDeletionAlerts FROM eServer

IF( @l_DeletionAlert = 0 )
BEGIN
UPDATE eAlert
SET eAlertType = N'~',
eAlertMessage = 'Deleted by Administrator',
eAlertTime = GETDATE()
WHERE eFolderID = @folderid
END
ELSE
BEGIN
DELETE FROM eAlert WHERE eFolderID = @folderid
END


__________________
Your best practice does not conform with my best practice.
0
nathangg

Member
Registered:
Posts: 24
Reply with quote  #6 
Thanks all for the help.  Here is how mine ended up:

CREATE procedure [dbo].[EKSH_upDeleteFolder]
(
@FolderId VARCHAR(31)
)
as
DECLARE @TableName varchar(31)
begin
SET @TableName = (SELECT eMap.eTableName FROM eMap,eFolder WHERE eMap.eMapName=eFolder.eMapName AND eFolder.eFolderId=@FolderId)
EXECUTE( 'DELETE FROM ' + @TableName + ' WHERE eFolderId=''' + @FolderId + '''' )
DELETE FROM eEvent WHERE eFolderID = @FolderId
DELETE FROM eWait WHERE eFolderID = @FolderId
DELETE FROM eAssignment WHERE eFolderID = @folderid
DELETE FROM eAlertRequest WHERE eFolderID = @folderid
DELETE FROM eAlertGeneratorLock WHERE eFolderID = @folderid
DELETE FROM eLog WHERE eFolderID = @folderid
DELETE FROM eRaisedFlag WHERE eFlagFolder = @FolderId
DELETE FROM eAlert WHERE eFolderID = @FolderId
DELETE FROM eFolder WHERE eFolderId = @FolderId
DELETE FROM eWait WHERE eFolderID = @FolderId
DELETE FROM eAttachment WHERE eKey LIKE '0'+CHAR(9)+@FolderID+'%'
UPDATE eFolder SET eParent='' WHERE eParent = @FolderId
end

GO


And here is how I did the cursor to loop through my query:


declare @efolderid nvarchar(31)
declare @rowNum int
declare eList cursor for


SELECT eFolderID
FROM [Metastorm76].[dbo].[eFolder]
where eFolderName like '%STEP 00%'



open eList
Fetch NEXT FROM eList into @efolderid
set @rowNum = 0
while @@FETCH_STATUS = 0
BEGIN
set @rowNum = @rowNum + 1
print cast(@rowNum as nvarchar(3)) + ' ' + @efolderid
exec dbo.EKSH_upDeleteFolder @efolderid;




fetch next from eList into @efolderid


END

close eList
DEALLOCATE eList

0
tspafford

Senior Member
Registered:
Posts: 64
Reply with quote  #7 
Quote:
Originally Posted by BMellert
Quote:

Quote:
DELETE FROM eAttachment WHERE eKey LIKE '0<tab>@FolderId%'

you would have to replace the <tab> with an actual tab - it gets changed in the post


Which can also be handled as
 ... LIKE '0'+CHAR(9)+@FolderID%' 

in SQL, or CHR(9) if Oracle, so the tab doesn't disappear if copy/paste.  (I've had that happen into some editors, so thought I'd offer this suggestion.)




I've used following:
 DELETE FROM eAttachment WHERE substring(eKey,3,31) = @FolderId



0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #8 
Quote:

I've used following:
 DELETE FROM eAttachment WHERE substring(eKey,3,31) = @FolderId



That will cause a table scan. Use like '0<tab>folderid%' to affect all attachments, or to find them.

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!