Upgrade Guide › Upgrading Databases › Upgrading a 6.0 Database › SQL Server: Post-Upgrade Change To Event Participant Table › 5. Run the Modify Table Script

5. Run the Modify Table Script

The section describes how to modify the Wgn3EventParticipant table.

To modify Wgn3EventParticipant

1. Open a new SQL Server Management Studio query window and run one of the following commands as a user with the sysadmin role (we recommend sa). This must be the same user that you used to run the pre-flight check (see step 2 in 'Run the Pre-Flight Check'):

   EXEC dbo.wgn_post_upgrade '<dir>'
   

   This command performs the entire table update as a single procedure. No further input is required.

   EXEC dbo.wgn_post_upgrade_1 '<dir>'
   

   This command runs part one of the table update, stopping at the point where all participant records have been copied to a file and Wgn3EventParticipant is ready to be truncated:

   Where <dir> is the target directory where the participant records will be copied to. In both cases, this must be the same folder that you specified in the pre‑flight check script (step 2 in 'Run the Pre-Flight Check').

2. (Only applicable if you ran the ‘part one’ script in step 1). The script terminates after copying participant records to the file system (see below). This allows you to confirm that data has been copied out successfully and to perform other administrative tasks.

   For example, you may want to back up bcp_pf_ep_data.txt.

   Important! If you do back up bcp_pf_ep_data.txt, leave the original file in its current location so that it can be reloaded!

3. (Only applicable if you ran the ‘part one’ script in step 1). In SQL Server Management Studio, run the ‘part two’ script, using the same user that you used to run the ‘part one’ script:

   EXEC dbo.wgn_post_upgrade_2 '<dir>'
   

   Where <dir> is the same directory that you specified in step 1.

What Does the Script Do?

In technical terms, the script performs the following tasks.

1. Prepares the database

   The script takes preparatory steps to restrict access to the table and to enable the participant address columns to be redefined. In particular:

   • Access to the database is revoked for the CA DataMinder primary user.
   • The database is switched from ‘multi user’ to ‘single user’ mode.
   • Relevant foreign key constraints and indexes on the Wgn3EventParticipant table are dropped or disabled.
2. Copies participant records to the file system
   1. First, the script changes the database from ‘single user’ to ‘restricted user’ mode. This enables the bcp utility to connect to the database.
   2. Next, it runs a bcp command to copy data out of the table to the bcp_pf_ep_data.txt file in the target directory. Progress is recorded in the bcp_ep_out.log log file.
3. Truncates the table

   This is the critical step! The script now truncates the Wgn3EventParticipant table, leaving it empty.

4. Sets the AddressUID columns to NOT NULL

   The script now makes the participant address columns mandatory.

5. Copies participant records back into the table
   1. First, the script sets the database recovery model to BULK_LOGGED mode. This eliminates the need to write Before and After images of each record to a log file during the copy operation.
   2. It then runs a bcp command to copy participant records from bcp_pf_ep_data.txt back into the Wgn3EventParticipant table. Progress is recorded in the bcp_ep_in.log log file.
   3. It verifies that the copy operation was successful, checking that the number of table rows copied out equals the number of rows copied in.
6. Reverses the previous preparatory steps

   Finally, the script reverses the earlier preparatory steps (see step 2). Specifically, it:

   • Sets the database from ‘restricted user’ mode back to ‘single user’ mode and then rebuilds any dropped indexes and re‑enables any dropped foreign key constraints.
   • Sets the database from ‘single user’ mode back to ‘multi user’ mode.
   • Resets recovery model back to whatever it was before (FULL or SIMPLE).
   • Restores the ability of the CA DataMinder primary user to connect to the database.

More information:

1. Run the Post-Upgrade Pre-Flight Check