Delete a Facility

•Method 1: Use Delete Facility Stored Procedure

•Method 2: Use Cascade Delete in EQuIS Professional

If a situation arises where a facility needs to be completely removed from an EQuIS Database, there are two methods for doing this. Prior to deleting a facility, EarthSoft highly recommends creating a current backup of the database before deleting a facility.

Warning: Deleting a facility is permanent and irreversible, and should only be done after careful consideration and planning. The only way to undo this operation is to restore a backup copy of your database.

Method 1: Use Delete Facility Stored Procedure

The EQuIS Database Schema includes a stored procedure that will delete a facility from the database. Delete a facility using the stored procedure by following the steps below.

1.Open the DT_FACILITY table to find the correct FACILITY_ID value of the facility you want to delete in EQuIS Professional.

Warning: Make sure the correct FACILITY_ID value is used to prevent inadvertently deleting the wrong facility.

2.Close EQuIS Professional. Check to make sure no other users are connected to the facility that will be deleted. Remove that facility access for inactive users.

3.Start SQL Server Management Studio and log in to SQL Server.

4.Expand Databases in Object Explorer.

5.Select the EQuIS Database that contains the facility to delete.

Warning: Select the correct database (if there are multiple EQuIS Databases), to prevent inadvertently deleting the wrong facility.

6.Click New Query on the toolbar and paste the query shown below using your FACILITY_ID value on line five (instead of 999999).

DECLARE @facility_id int
DECLARE @row_count int

-- MAKE SURE TO USE THE CORRECT FACILITY_ID VALUE!!
SET @facility_id = 999999

/*
If you receive the following error message:

Msg 207, Level 16, State 1, Procedure esp_table_order, Line 25
Invalid column name 'id'.

then first execute this procedure before executing the esp_delete_facility procedure:

EXECUTE dbo.esp_table_order
*/

EXECUTE equis.delete_facility  --NOTE: if database is prior to v6 change line to  EXECUTE dbo.esp_delete_facility
   @facility_id
  ,@row_count OUTPUT

-- SHOW THE NUMBER OF ROWS THAT WERE DELETED
SELECT @row_count
GO

7.Deleting a facility cannot be undone. Make sure you really want to delete it.

8.Execute the query to delete the facility.

If the message below is received,

Msg 207, Level 16, State 1, Procedure esp_table_order, Line 25

Invalid column name 'id'.

first execute this procedure before executing the equis.delete_facility procedure.

EXECUTE dbo.esp_table_order

Note: In databases with modules that require additional Schemas added (e.g., Collect or DQM), continue onto Method 2 below to delete data from datasets meeting the following conditions:

•a table with FACILITY_ID has child tables that reference that table, and

•those child tables do not themselves contain the FACILITY_ID column.

Method 2: Use Cascade Delete in EQuIS Professional

Warning: References to a facility are much more complex than for most tables due to inability to have foreign key constraints on some FACILITY_ID fields that can also reference facility group IDs as well as to records in DT_FACILITY.

Because the Professional Cascade Delete functionality relies on foreign key constraints, this method is not the best way to delete a facility from an EQuIS Database. Using the EQUIS.DELETE_FACILITY stored procedure as described in Method 1 should be used instead. However, if the desired action is to delete only those fields that have a foreign key constraint, then this method can be used.

EQuIS Professional includes a Cascade Delete button that is enabled when logged into the database as a database owner. Delete a facility using the Cascade Delete button by following the steps below.

1.Check and make sure no other users are connected to the facility that will be deleted. Remove that facility access for inactive users.

2.Open EQuIS Professional and log in to the same database, but to a different facility than the facility that will be deleted.

3.Open the DT_FACILITY table using the Table (All Rows in Database) option.

4.Select the row for the facility to be deleted.

5.Click Cascade Delete on the toolbar.

6.Deleting a facility cannot be undone. Make sure you really want to delete the facility.

7.Click Yes to confirm the facility will be deleted.

8.The process of the rows for that facility being deleted from all tables in the database may take several minutes.

9.Review the list of rows that have been deleted (right-click on the grid and save summary information to an .xml file for future reference).

10.Click Finish to commit the transaction, and permanently delete the rows. (Click Cancel if deleting the facility is no longer desired).