This KB entry details how to install and setup an MS SQL and prepare it for use with DataPA via a OpenEdge AppServer. Before you begin you must make sure that you have the appropriate OBDC drivers installed on the machine that the OpenEdge DataServer’s will be running on.

This KB entry deals with the MS SQL DataServer but the process is very similar for the Oracle DataServers.

For detailed documentation on setting up OpenEdge DataServers please refer to the documentation on Progress’s website:

1. The first step is to install the Dataserver license(s) on the machine you are using. To do this you will need to start by closing down all Progress processes on the machine you are going to perform the install on. To do this you will need to stop the AdminServer. The screen below shows how to stop this service in windows but you could also use the command proadsv –stop at the command line. NB: There may also be other Progress processes running in adition to those controlled by the AdminServer and they will all need to be closed to allow the installation to continue.
   
   
   
2. Next run the ‘License Update’ option from the menu OpenEdge menu or use the original setup.exe by double clicking on it. This will start the OpenEdge. NB: If you are double clicking on the seup.exe then please ensure that it is the original installation setup.exe for the version of OpenEdge which you got the DataServer licenses for.
   
   
3. Next you will be asked to enter the serial number and control codes for the Progress products that you are installing. Enter these and click the Accept button and then Next to continue with the installation.
   
   
   
4. Once the DataServer licenses have been installed then you can restart the AdminServer either using the Services window used above to stop the AdminServer service or by using the command proadsv –start at the command line.
   
   
5. Next a System DSN needs to be setup which the OpenEdge DataServer will use to communication with the MS SQL database. To do this select ODBC or Data Sources (ODBC) from the Control Panel. This will open the window below:
   
   
   
6. Select the System DSN tab and click the Add button as you are going to add a new one to be used by the OpenEdge DataServer.
   
   
7. This will open a new window like the one below. In this example you will select SQL Server and use the OpenEdge MS SQL DataServer so we select SQL Server here and click Finish.
   
   
   
8. Next you need to give a name to the System DSN as well as a description and also select the server that the SQL database is running on. The Server drop down list should be populated for you. Once you have selected a name and server then click Next.
   
   
   
9. In the next step you can choose how to authenticate the user when connecting to the SQL database. For this you must select ‘With SQL Server authentication using a logion and password provided by the user.’ as in this use of the DataServer the connection will be made by the AppServer process not by the Windows client so Windows Authentication would not work. Click Next once you have selected the option and entered a valid Login ID and Password which is valid on the SQL Server you are connecting to and which also has access to the database that you want to use.
   
   
   
10. In the next step if the database showing is not the one you want to use then click the check box ‘Change the default database to’ and select the database you want to use from the drop down list. Then select the other options as required and click Next.
    
    
    
11. On the next screen select the options required and then click Finish.
    
    
    
12. Now a screen should pop up giving details of the System DSN that is going to be created. Review these details and once satisfied click ‘Test Data Source …’
    
    
    
13. You should receive a message that the connection is successful like the one below:
    
    
    
14. Click OK and then OK again and you have successfully setup your System DSN which should now be displayed in your list.
    
    
    
15. Next you need to create a new database which will act as the schema holder for this MS SQL database. To do this start the OpenEdge Command Line using the Proenv option on the OpenEdge menu. Then change directory to the directory which you want the schema holder database to reside. This database will not contain data just the OpenEdge version of the SQL Server database schema so it will not be especially large. Then use the prodb command to create. NB it is best to use a UTF-8 empty OpenEdge database as the MS SQL Server database may contain UNICODE data types so the command that should be used to create the database is:
     prodb <db-name> %DLC%\prolang\utf\empty
    
    
16. Next you need to start an OpenEdge session and connect to our newly create empty database.
    
    
17. Once the database is connected you need to go into the Data Administration screen and from the menu in there select DataServer > MS SQL Server Utilities and the Create DataServer Schema.
    
    
18. This will open up the window below where you can enter the logical database name that you want to use as well as the codepage (in this case utf-8) and also the ODBC Data Source Name which is the System DSN you set up earlier in this KB entry. Once you have entered these values then click OK
    
19. Next you will be prompted for a User ID and Password. Enter the values for a user who has access to the SQL Server, the database you are setting up and who also has SELECT permissions setup within the MS SQL database for the database tables and other databases objects that you will want to use.
    
    
20. The next window allows you to select which of the database objects you want to consider for load into the schema holder database. You can select here by object name, owner or qualifier.
    
    
21. The next window displays details of the database objects that are available in the MS SQL database.
    
    
22. Click the Select Some button and the following window is shown which allows you to select which database objects you want to import into the schema holder database. You can select by name, owner or object type using pattern matching:
    
    
23. Now we should see those database objects that we selected in our list:
    
    
24. Next when you click OK the MS SQL definitions you have selected are loaded into schema holder database. And you should be able to see the tables in the OpenEdge Data Dictionary:
    
    
25. You should also be able to write a simple ABL procedure to display data from a table in the MS SQL database:
    
    
    
26. Now you have your OpenEdge DataServer setup the next step is to setup a database server for the schema holder database so that multiple users (AppServer Agents) can use it at the same time. To do this it is exactly the same process as with normal OpenEdge databases. You can use the Progress Explorer to add this, use dbman or even the proserve command. We would recommend doing this through the Progress Explorer. So on the Databases node right click and select New.
    
    
27. This then opens a window where you can enter a name for the new database. Just call it the same name as was used for the database file itself. Once you have entered that then click OK.
    
    
28. Next enter the path to the database, select whether you want the database to start automatically when the AdminServer is started and then click OK.
    
    
29. Next expand the newly added database node for your new database and select the defaultServerGroup. Right click on this and select Properties.
    
    
30. This will open the window below which will allow you to assign a port number for the database. Enter one and then click OK.
    
    
31. Now you can right click on the new database and select Start to start the database server.
    
32. To make sure that the database server started correctly right click on the database and select Status
    
    
33. The database status screen should then be displayed as below:
    
    
34. Now that the database is up and running you can now turn your attention to the AppServer. You should create a parameter file that will be used by each AppServer Agent to connect the required databases. This will include your schema holder and associated MS SQL database as well as other database whether they are OpenEdge, DB2, Oracle or even other MS SQL ones. There would just be multiple –db statements to connect these various databases.
    For example if we wanted to create a parameter file that connected to our schema holder and associated MS SQL database the it would look like this one (above) where <SQL DB User ID> is the user used to connect to the MS SQL database and <SQL DB Password> is the password of the user used to connect to the MS SQL database.
    
    
35. Test the parameter file to ensure it works by right clicking on it and selecting Start Client. This should start up an OpenEdge session and connect the databases.
    
    Next you need to setup an AppServer to use with DataPA. This will use the parameter file we have created here to connect the various database to the AppServer Agents on startup.
    
36. The rest of the setup is the same for any DataPA AppServer. Remember to add the appropriate DataPA procedure library to the PROPATH of the AppServer.