To create and configure the OpenEdge DataServer for Microsoft SQL Server or Oracle database required by the replication system you must configure the Open Database Connectivity driver, create a schema holder, and create target database in Microsoft SQL Server.

Standard data-type conversions

Pro2 performs some data type conversions automatically to circumvent issues that would typically break other types of Open Database Connectivity (ODBC) data transfers from OpenEdge to an Microsoft SQL Server.
  • Date conversions— Some versions of Microsoft SQL Server and Oracle databases cannot handle dates with a value before January 1, 1753. To work around this issue, Pro2 preserves the month and the date and sets the year to 1753.
  • Column width—In OpenEdge databases, data is stored in variable length fields, regardless of the field width definition. However, in Microsoft SQL Server and Oracle databases, column width is fixed. To work around this, Pro2 truncates data to the column width that is defined in the target schema.
  • Logical fields—The OpenEdge database recognizes three possible values for logical fields: True (1), False (0), and Unknown (?). The value Unknown is not recognized in other databases. To work around this, if a logical value is True, then 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 before you configure the OpenEdge DataServer. This allows communication between the OpenEdge database and another database.

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 Add to add a new system DSN.


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




  4. Specify the name (for example logical-db-nameODBC) for your data source and the server where it is located, then click Next.


  5. Provide your login information. If you are using Select SQL authentication, provide the login ID and password for the Microsoft SQL Server 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 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 Microsoft SQL Server DataServer. The schema holder is the layer that allows ABL to interact with the Microsoft SQL Server Database as though it were a native OpenEdge database.

To create a schema holder:

  1. Open a Proenv window, and type prodb sportssh empty to create an empty schema holder database.
  2. 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, and specify the 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, 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 Microsoft SQL Server

To create a target database in Microsoft SQL Server:

  1. Open Microsoft SQL Server Management Studio.

    The Connect to Server window appears by default.

  2. Choose Windows Authentication, or Microsoft SQL Server Authentication if you have a SQL Server username and password.
  3. Click Connect.

    The Object Explorer area appears.

  4. Right click the Databases folder. Click New Database

    The New Database Window appears.

  5. Enter the Database Name, for example, sports. Click OK.

    The database is now created. It appears under the Databases folder.

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