Please simplify the folder removal process from an X3 database.
As of version 6, the process of deleting a folder is more than simply pressing the delete button within the folder function (GESADS). In order to delete a folder you must do the following:
1. Depublish the folder in the admin console (part of the web server functionality)
2. Go to Windows Explorer and find the folders\X3_pub directory and delete that file folder
3. Delete the data folder within Windows that corresponds to step 2
4. Launch SQL Server and perform the following
a) Drop all logins associated with the folder. At a minimum that includes the folder name correlating to the login as well as the folder_report login as well.
b) Drop all tables within SQL that correlate to this specific login
c) Drop the user from the database
5) Delete the folder within the folders function (GESADS)
In order to increase simplicity, and decrease opportunity for error, it would be advantageous to increase the functionality of the folder delete process within the folders function to handle the process of removing the tables in X3, and handling the logins and users within a database. This would save novice SQL users from making mistakes during the table removal process and simplify the experience.
Individuals that want this functionality are those that are trying to keep the size of their database down. Currently, copying folders is a normal process of the piloting procedure for any X3 implementation. More often than not, more than one pilot folder is required. Older pilot folders become stale or obsolete and should be removed.
by: Bob D. | over a year ago | Technical
Comments
Agreed this part should be a button click with confirmation in the SAGE admin tool. There are SQL scripts available to help you do the SQL clean up, see here.
/* SAGE ERP X3 folder data cleanup script */
/* Author : GBE */
/* Set the FOLDER_NAME with the name of the folder to be deleted */
/* Sep 2014 : sequence cleanup */
/* Aug 2018 : add 'use db_name' (BLE) and explanations */
/* Feb 2019 : remove '' around database name as it makes error on SSMS 17.9.1 */
DECLARE @FOLDER_NAME nvarchar(30),@sql NVARCHAR(150),@tbl NVARCHAR(100),@sqldrop NVARCHAR(70);
/* Replace 2nd FOLDER_NAME by the name of your FOLDER */
SET @FOLDER_NAME = 'FOLDER_NAME'
/* Replace db_name by the name of you SQL database */
use db_name
/* Drop all views */
while
(SELECT count(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='VIEW') > 0
begin
SELECT top 1 @tbl = TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='VIEW'
SET @sql = 'drop view ['+@FOLDER_NAME+'].['+@tbl+']'
exec sp_executesql @sql
end
/* Drop all tables */
while
(SELECT count(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='BASE TABLE') > 0
begin
SELECT top 1 @tbl = TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='BASE TABLE'
SET @sql = 'drop table ['+@FOLDER_NAME+'].['+@tbl+']'
exec sp_executesql @sql
end
/* drop all sequences */
while
(SELECT count(SEQUENCE_NAME) from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = @FOLDER_NAME ) > 0
begin
SELECT top 1 @tbl = SEQUENCE_NAME from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = @FOLDER_NAME
SET @sql = 'drop sequence ['+@FOLDER_NAME+'].['+@tbl+']'
exec sp_executesql @sql
end
set @sqldrop = 'DROP USER ['+@FOLDER_NAME+']'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP USER ['+@FOLDER_NAME+'_REPORT]'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP SCHEMA ['+@FOLDER_NAME+']'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX]'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX_H]'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX_R]'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX_RH]'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP LOGIN ['+@FOLDER_NAME+']'
exec sp_executesql @sqldrop
set @sqldrop = 'DROP LOGIN ['+@FOLDER_NAME+'_REPORT]'
exec sp_executesql @sqldrop
GO