Is there a way to batch delete Enterprise Structure Locations and Categories?

[kevin_christens]

We would like to restructure how the locations and categories are organized and would like to delete all existing location and category paths to reload new ones.

Is there a simple batch process that can do this since doing individually via the web interface would be too time consuming?

Example would be a stored database routine similar to deleting users in bulk with ComplianceUserRemoveBatch.

[Alpesh_Gohil]

Hi, 

Please reach out to Support. They can help with providing the SQL. There are SQLs that have been used to perform cleanup for Cloud customers, which can be used for any OnPrem environments too.

Thanks!

[ChrisG]

Here's an example of a SQL script that can be executed against the compliance database to delete all groups identified by the first query in this script:

-- This script will delete all enterprise groups identified by the following query:

SELECT GroupID
INTO #GroupsToDelete
FROM dbo.GroupEX_T
WHERE
	GroupTypeID = 1			-- 1 = locations, 2 = corporate units, 3 = cost centers
	AND BusinessView = 0	-- don't delete the root group

DECLARE db_cursor CURSOR FOR
SELECT GroupID from #GroupsToDelete ORDER BY GroupID

OPEN db_cursor

DECLARE @ID int

FETCH NEXT FROM db_cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @ID

	EXEC dbo.GroupDeleteAndChildren @groupid = @ID

	FETCH NEXT FROM db_cursor INTO @ID
END

CLOSE db_cursor
DEALLOCATE db_cursor

[Andrei_ABB]

Hi Chris, 

Has this script been updated for changes in new FNMS versions? I'm getting 

Invalid column name 'GroupExID'.
Procedure GroupDeleteAndChildren, Line 24 [Batch Start Line 0]
Invalid column name 'GroupExID'

I'm using FNMS 2022 R2

Maybe this procedure is now deprecated and we now have something new? Probably a column name changed by the message. 

Regards,

Andrei

[ChrisG]

I don't know of any changes, but it is possible the GroupDeleteAndChildren stored procedure does not work anymore. This stored procedure is not actually used by any functionality within FlexNet Manager Suite, so it could be deprecated.

Or is it possible that you have a customized stored procedure configured in your database? The "line 24" referenced in the error message seems a strange: I can't see how anything around line 24 in the stored procedure would obviously result in the error you are seeing.

If this procedure doesn't work anymore, new SQL script would be required to achieve this. I'm not sure if anybody here has a script to share.

[Andrei_ABB]

Hi, 

Update: I was able to get  a different script that doesn't use the "GroupDeleteAndChildren" stored procedure and works with FNMS 2022 R2. The only drawback is that it only works for costcenters since it removes all the references of costcenters in other tables. For anybody needing to delete something else like location or corporate unit, you can probably substitute costcenterid with locationid or corporate unitid in each of the update sections and will do just fine. Of course don't forget to test this on your test instance. I hope this will be useful for others looking for this in the future. 

Regards,

Andrei