Troubleshooting > Altering Schema Tables

There are several different reasons why you might find it necessary to make changes to your Schema data tables. One might be to alter the defined size of a segment data column to avoid data truncation issues.

Dynamically adding columns. The Schema Engine's inbound processors will detect the existence of columns at runtime! Here's a scenario which might sound absurd but stranger things have happened. Let's consider field #8 of the PID segment of an HL7 message (the patient's gender). In the HL7 standards this field is an HL7 data type ST (string data) which has 1 component. When the Schema Engine created your schema tables it created 1 column in the <prefix>_SEGMENT_PID_A table named PID_F8_C1 (it also created a column called PID_F8_C0 see how C0 columns work) to hold the data. Now you discover that your trading partner has decided to play fast and loose with the HL7 standard and sends you something more in this field.

•Example A: A normal field # 8 would look like this in the RAW HL7 |F| (the F indicating "Female")

•Example B: Our wayward trading partner sends us something that looks like this - |F^Female^Mayberry General Hospital|. They've decided to add a second and third component to this HL7 field and put a description of the patient gender code (F) and the hospital name where the patient was born.

The problem for you is that in the <prefix>_SEGMENT_PID_A table there is only 1 component data column (PID_F8_C1).

Manually Altering Schema Tables

A quick solution for you (assuming that you don't want to go to the trouble of altering your HL7 vendor definition and regenerating your schema tables) would be to go into SQL Server itself and MANUALLY alter the <prefix>_SEGMENT_PID_A table and add two new columns yourself called PID_F8_C2 and PID_F8_C3. NOTE: If you do something like this you MUST insure that the columns are either a Varchar or Text column type and that they either Allow Nulls or have a SQL Default Value. WARNING: When adding columns manually you cannot use the new(ish) MAX predicate when specifying the column size in SQL (it is not supported by the schema engine). When inbound processors are running they will automatically detect these columns and will add the HL7 data properly so that for Example B:

•PID_F8_C1 = F

•PID_F8_C2 = Female

•PID_F8_C3 = Mayberry General Hospital

IMPORTANT NOTE: IF you do something like this, we highly recommend that you document this change thoroughly and keep it. This way you will know that you have to manually make these changes if you ever need recreate your schema tables for this trading partner. Ideally, we recommend that once you have determined what change(s) you want to make, that you SCRIPT those changes and run them natively (rather than using the table "design" mode in the SQL Management Studio. You can then archive those SQL Scripts so that they can be run again if you ever need to quickly regenerate (or redeploy) your schema tables (see creating schema tables).