How to add a column to the database using the Database Schema Editor

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	Supportworks Version 7.5.0 onwards

How to add a column to the database using the Database Schema Editor

This can be done in 4 simple steps.

• Export schema from physical database
• Import schema into db_config
• Add column to database schema
• Apply schema changes to physical database.

Prerequisites

Before making any changes you will need to stop the SwServerService from the Supportworks Server Configuration application. Please note that this can cause loss of data and that we would strongly recommend that a full backup be taken prior to proceeding.

Export schema from physical database

Run Supportworks database Schema Editor.

1. From the Database menu select “Export Specification from physical database…” or click the icon. Make sure that the Database field has the name of the database that you wish to

amend selected. Then click next.

1. Make sure the swserverservice is selected. Click next.

1. Select any valid destination to export the file to for what we are doing it does not really matter where this file is stored (do not overwrite the swdbconfig.xml file).

1. If you wish to view the database config in xml tick the “View exported file” tick box. Then click Finish.

Import schema into db_config

Close the IE window if you have viewed the schema.

1. Once the database schema has been exported from the physical database a popup will appear asking you if you want to import the schema specification database.

1. Click Yes to update the schema within the Database Schema Editor.

1. Select the schema that you have just saved. (this will appear filled in by default.) Click next.

1. Make sure you database name is correct for the database you wish to use. Click next.

1. Make sure the “Do a clean import to replace the existing specification” is selected. Click Finish. The schema in the main window will update.

Add column to database schema

Expand the schema tree view for the database that you wish to amend and select the table that you wish to add a column to. (in the example below we are going to add a WEB_ADDRESS column to the MANUFACT table)

1. Select a table and then click on the New Column menu item
2. Then perform the following steps:

• Enter the name of the column that you wish to add.
• Select the type of column you want within SQL Data Type.
• Enter the data size for the column in the Max Data Size field.
• Select any of the tick box options that you require.

Note: The tick boxes are context sensitive so they may be greyed out depending on the data type you have selected. Note the new column in now visible within the columns window.

Apply schema changes to physical database.

The next step is to apply the changes that you have made to your physical database

• Either select “Apply Specification to Database…” from the Database menu or click the icon.

The screen that informs you that your changes are about to be applied to the physical database will appear.

• Tick the box that says "Allow type changes". Click Apply and then say ok to the next popup.

A progress bar appears and when this progress bar has disappeared your changes have been made to the physical database. Restart the SwServerService. You will now be able to use the new column from within Supportworks.