To create and configure the OpenEdge DataServer for MS SQL Server or Oracle required by the replication system, perform the following steps:
  • Configure the ODBC driver.
  • Create a schema holder.
  • Create target database in MS SQL Server.

Standard data type conversions

Pro2 performs some data type conversions automatically to get past issues that would typically break other types of ODBC data transfers from OpenEdge to an MS SQL database server.
  • Date conversions— Some versions of MS SQL server and Oracle cannot handle dates with a value before January 1, 1753. To work around this situation, for any dates before 1/1/1753, Pro2 preserves the month and the date and set the year to 1753.
  • Column width—In OpenEdge databases, data is stored in variable length fields regardless of the field width definition. However, in MS SQL server and Oracle, column width is fixed. To work around this, Pro2 truncates data to the column width defined in the target schema.
  • Logical fields—OpenEdge database recognizes three possible values for logical fields: True (1), False (0), and Unknown (?). The value “Unknown” is not recognized in foreign databases. To work around this, if a logical value is True, Pro2 sets the target field to True, otherwise it is set it to False.

Configure the ODBC DataServer

The ODBC connectivity must be set up prior to configuring the OpenEdge DataServer. This allows communication between the OpenEdge Database and foreign database. Perform the following steps to configure the ODBC DataServer:

  1. Add a new system DSN using the Windows Data Sources tool by selecting the System DNS Tab in the Data Sources (ODBC) Administrative Tools section in the Control Panel.
  2. Click the Add button to add a new System DSN.


  3. For MSSQL Server, choose the SQL Server driver; for Oracle, select the Microsoft ODBC for Oracle driver and click Finish.




  4. Specify the name (for Pro2 typically <logical-db-name>ODBC) for your data source and the server where it resides and click Next.


  5. Specify login information. If using Select SQL authentication, provide the Login ID and Password for the MS SQL Database, and click Next.


  6. From the Change the default database to drop-down, select the appropriate database, and click Next.


  7. Accept the defaults on the next dialog box and click Next.
  8. The next dialog box has a button for testing the connection. When you select the button, a message is returns indicating success.
  9. Exit the Data Source administration tool.

Create an OpenEdge schema holder

An empty OpenEdge Database is created to act as the schema holder for the MSS DataServer. The schema holder is the layer that allows ABL to interact with the MS SQL Server Database as though it were a native OpenEdge Database.

Perform the following steps to create a schema holder:

  1. In the Proenv window, type prodb sportssh empty to create an empty schema holder database.
  2. Now start a single user session by typing prowin sportssh -1. The Procedure Editor window appears.
  3. From the Procedure Editor, select Tools > Data Administration.
  4. Select DataServer > MS SQL Server Utilities > Create DataServer Schema . The Create/Modify Record for MSS Schema window appears.
  5. Specify Logical Database name as sportssql, ODBC Data Source Name as sportsodbc. Click Ok.
  6. Enter the Login ID and Password if required in the next dialog box and click OK.
  7. Enter criteria for Object Name, Object Owner (typically, but not always “DBO”), and Qualifier.
  8. Select the Default to OpenEdge DATETIME option.
  9. If you are using LOB fields in replication, ensure that the Default to OpenEdge LOB for BLOBs option is selected along with the CLOBs option. This forces the data server to automatically consider certain target side data types to be treated as LOBS.

    By selecting CLOB, varchar(max)/nvarchar(max) data types are automatically converted to CLOB. By selecting BLOB the data server is forced to consider all varbinary(max) as BLOB. On source fields that are character types, Pro2 may convert some types to varchar(max) when the source width is wider than the MAX_CHAR_WIDTH property setting. This conversion has the added benefit of avoiding row size limits for SQL Servers. However, varchar(max)/nvarchar(max) are not necessarily treated as CLOBS.



  10. Click OK to finish.

Create target database in MS SQL Server

Perform the following steps to create a target database in MS SQL Server:

  1. Open Microsoft SQL Server Management Studio. The Connect to Server window appears by default.
  2. Choose Windows Authentication or SQL Server Authentication if you have a SQL Server username and password.
  3. Click Connect. The Object Explorer area appears.
  4. Right click Databases folder. Click New Database… The New Database Window appears.
  5. Enter Database Name as sports. Click OK. The sports database is now created. It appears under the Databases folder.

You can now configure the replication process and proceed with the replication.