Consultor Eletrônico

Status: Unverified

GOAL:

How to change field's extent using MS SQL Server DataServer on both schema holder and on the MS SQL Server?

GOAL:

Is there a way to increase the extent of a field on the schema holder as well as on the MS SQL Server table?

FACT(s) (Environment):

Windows
OpenEdge 10.x
MS SQL DataServer

FIX:

OpenEdge database itself does not allow modifying the extent field to increate the extent. The existing extent field on the OE database needs to be deleted and then create another extend field with the modifed extent size. On the other hand the MS SQL Server does not have extent concept; therefore, the protoMSS process creates as many fields on the MS SQL Server as the number of extent for the field. The field is marked with <field name>##1, <field name>##2...<field name>##n. Each of the extent field are created on the MS SQL Server in an ascending order as

<field name>##1
<field name>##2
...
<field name>##n

MS SQL Server does not allow adding a field (column) in between existing columns. Therefore, there are two delta processes need to performed, and each time the .pf file and the .sql file needs to be updated or run to update the schema holder and to update the MS SQl Server table.

Here are the 2 step processes (Before you run the delta.df process make sure to backup the OE DB, schema holder.df and MS SQL Server DB and/or the tables' records those are intended to be changed):

First Step:

1. Generate a delta.df between two OE databases (db1 and db2) where the extent field from db2 database table is deleted. You need to be connected to both OE db1 and db2 databases, and the modified db2 needs to be selected. Otherwise, the delta.df will contain ADD statement instead of DROP statement.
2. Open the delta.df and review to make sure only the DROP statement is there correctly.
3. Run the DataServer> MS SQL Server utilities > Schema Migration Tools > Generate Delta.sql OpenEdge to MSS...
4. Browse the delta.df file by clicking the "Files..." button, specify the MSS object Owner name (mostly dbo unless the tables are owned specific user) and select appropriate options.
5. Click OK to generate the .df for the schema holder and .sql file to run on the MS SQL Server.
6. Apply the schema holder .df to the schema holder and .sql to the MS SQL Server DB.

At this point the extent field(s) is/are deleted from schema holder, all all the column(s) associated the the extent fields are deleted from the MS SQL Server. The records are also lost on the physical OE DB and the MS SQL Server DB.

Second Step:

1. Delete the same extent field from db1 table. If you do not delete the same extent field from db1 table, the delta.df file will contain an extra line DROP field statement at the very begining before ADD field statement. In this case you manually need to delete the DROP FIELD statement from the beginning before you run it against the schema holder.
2. Add the same field on the db2 table with higher extent value
3. Generate delta.df between db1 and db2 databases. You need to be connected to both OE db1 and db2 databases, and the modified db2 needs to be selected.
4. Examine the delta.df to see if the new extent field is listed correctly with ADD statement.
5. Run the DataServer> MS SQL Server utilities > Schema Migration Tools > Generate Delta.sql OpenEdge to MSS...
6. Browse the correct delta.df file by clicking the "Files..." button, specify the MSS object Owner name (mostly dbo unless the tables are owned specific user) and select appropriate options.
7. Click OK to generate the .df for the schema holder and .sql file to run on the MS SQL Server.
8. Apply the schema holder .df to the schema holder and .sql to the MS SQL Server DB.

Now the schema holder and the MS SQL Server database will have a new field/columns.