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.
Apr 14, 2022 01:05 PM
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.
Apr 14, 2022 04:55 PM
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
Apr 29, 2022 01:39 AM
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.
May 24, 2023 06:23 AM
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.
May 24, 2023 09:28 PM
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.
May 26, 2023 08:54 AM - edited May 26, 2023 08:56 AM