CoLinear Systems R4W_Conversion.doc
Response for Windows Conversion
Legacy (v5.x) → Response for Windows (v6.3)
Pre-requisites:
· Response v5.1 or 5.2
o Purge old data from Legacy Response system before starting the conversion.
· Pre-installed target database (SQL)
o Pervasive.SQL, or MS SQL Server (7.0 or 2000), or MSDE[1], or Oracle
· Pre-installed database driver for your database
o MS SQL Server (or MSDE) – Mertech Driver, trial version can be downloaded for conversion
o Pervasive.SQL – Data Access Pervasive Driver
Conversion Overview:
· Install Required Components on Server
o Install and Configure Database Engine – A
§ MS SQL Server
§ Pervasive.SQL
o Install and Configure the Pervasive Workstation Engine – B
o Install Response For Windows (Application) – C
o Install Response For Windows (Database) to MS SQL Server – D
o Register Database Drivers – E
§ SQLFlex 7 from Mertech Data Systems
§ Pervasive driver from DataAccess
o Migrate Response Legacy data to Response For Windows – F
§ MS SQL Server
§ Pervasive.SQL
o Identify and Correct Orphan Records – G
o Post-Conversion Cleanup – H
· Install Required Components on each Client
o Install and Configure Client components for Database – A
§ MS SQL Server
§ Pervasive.SQL
o Perform Response For Windows Workstation Setup – I
A. Install and Configure Database & Client Components
Please have your network administrator or outside network service provider install and configure your database engine and client connectivity components. Currently your choices are MS SQL Server, MSDE or ORACLE. MSDE is a free baby version of MS SQL Server. Pervasive SQL (PSQL) is only an option if you are currently using legacy Response with PSQL. This is not something CoLinear Technical Support can do for you, nor is it something you should attempt yourself unless you’re already familiar with network and database configuration and setup procedures. We strongly recommend you plan ahead to be sure you’ll have access to expert help in case it’s needed. Please remember … we only provide the tools, not the expertise for configuring your unique computing environment to use them. That responsibility lies entirely with you.
IMPORTANT! Bear in mind that with the exception of the Response for Windows Workstation Setup, all actions described in this document are to be performed directly on the server or, for example, in the case of a local Pervasive.SQL conversion, on the standalone PC whose role it is to act as the server for the purposes of conversion.
For those who will be using Pervasive.SQL, we recommend that you use the Pervasive Workstation Engine for the conversion because it runs faster. This can also be downloaded from Pervasive’s FTP site. However, once the conversion is complete you should completely uninstall the Workstation Engine and use the regular Server Engine (or Workstation Requester). Using the Workstation Engine to access the server database across the network can cause problems.
MSDE (the free baby version of MS SQL Server, you must pre qualify to use this))
For those who will be using MSDE, we’ve included the MSDE 2000 software on your R4W Install CD. However, you may need some expert assistance to configure it properly. To Install MSDE on your server (or workstation designated as “the server” on your peer-to-peer network):
1. Insert the R4W Install CD [NOTE: this CD will not auto-run]
- At the server, select Windows Start | Run | Browse… option and double-click on
“E:\ msde_setup_to_drive_c.bat”, where “E:” is the drive letter of your CD-ROM. (If you would like to have the MSDE Server installed on the D drive of your server rather than the C drive double-click on “E:\ msde_setup_alternate_to_d_drive.bat” instead.)
NOTE: This process may take 10 minutes or longer to complete so please be patient.
3. When the install has finished, you must SHUT DOWN and REBOOT the server before continuing to the next step.
4. Start the Microsoft SQL Server Desktop Engine. Double click on the Service Manager icon in your system tray (bottom right of your screen). to bring up the following dialog.
Be sure to check the “Auto-start service when OS starts” checkbox, and click on the Start/Continue button. This ensures that MSDE will always start when you reboot your machine.
MS SQL Server
For those who will be using MS SQL Server please have SQL Server Database Administrator make the following configuration changes:
· Using Enterprise Manager go to the Security tab in the SQL Server Properties window. Choose “SQL Server and Windows” as the authentication method.
[Step B below is only for customers converting from Pervasive.SQL. All others should skip to Step C “Install Response for Windows (application)”].
B. Install and Configure the Pervasive Workstation Engine
The Pervasive Workstation Engine is used to run the R4W Conversion. It should be installed on one machine only and uninstalled once the conversion is complete.
The default setting of the Pervasive Workstation Engine interferes with Pervasive Client Requester. If the Workstation Engine is used to access server data it will cause a lock on the database. Do not install the Workstation Engine on anything other than the one machine that you use to run the Conversion. Follow these instructions:
First you need to uninstall the Pervasive Client Requester (if installed) from the machine. You do this via the Windows option to Add/Remove programs in the Windows Control Panel.
Installation
- Insert the “Response For Windows” CD
- Run “E:\ psqlwe\Setup.exe” (where “E” is the drive letter of your CD-ROM)
- Select the “Typical” install configuration and click “Next” to proceed to the “Select Installation Directory” screen.
- Accept the default installation directory and click “Next” to install the Transactional and Relational Interfaces.
- Click “Next” to perform the communication tests. Refer to the Pervasive Install Documentation if any of these tests fail.
- Click “Finish” and restart the computer.
Configuration
- Start the “Pervasive Control Center” by going to “Start \ Programs \ Pervasive \ Pervasive Control Center”
- Open the Configuration Utility by going to “Center \ Configuration Utility”
- Expand the tree under the “Engine” category and select the “Access” folder.
- Modify the following settings and apply your changes by right-clicking on the setting selecting the desired action.
- Logical File Handles = 500
- Maximum Open Files = 500
- Once you have made these changes reboot the computer so that the engine is restarted and the changes take effect.
- The Pervasive Workstation Engine is now properly installed and configured.
This
space
intentionally
left
blank.
C. Install Response For Windows (application)
NOTE: If you haven’t done so already, please SHUT DOWN and REBOOT the server before continuing below. Be sure to perform the following on the “server” (not a workstation).
1. Insert the “Response For Windows” CD in your CD-ROM drive or find the previously downloaded file “ResponseForWindows.exe” then click Okay and Continue.
[NOTE: The CD will not auto-run.]
2. Select Start|Run|Browse and double-click on “ResponseForWindows.exe.”
3. Click “Microsoft SQL Server New Install” if you already have or are converting to MS SQL Server or MSDE. {For the purposes of this document, any reference to SQL Server is also a reference to MSDE}, or click “Pervasive SQL New Install” if you are converting to Pervasive.SQL.
4. Click “Next” to proceed to the “Choose Destination Location” screen.
5. Click “Next” to accept the default install location and install the Response For Windows application files. [Please do NOT change the default install location unless you have an excellent reason for doing so and will remember to refer to this other location when we refer to the default.]
6. Response for Windows requires a valid license key file named userinfo.key. This file was included on a diskette with your installation materials labeled “Response for Windows x-User License Key” or emailed to you separately. If emailed to you, copy the file to a diskette or work directory accessible to your conversion machine.
Insert the floppy disk in your drive and accept the default A:\ prompt, (or enter the location where you saved it) and click “Next.” When complete, click Finish.
IMPORTANT! You cannot launch Response for Windows until the above license key is successfully installed. Please contact us immediately if you have any difficulties here.
- (if applicable) – install the updates in the \Updates folder of the CD-ROM to update Response for Windows to the latest build. The “Build” number will be written on the label of the CD-ROM. If a later build is listed in the \Updates folder of the CD-ROM (e.g. 1061 is listed on the CD-ROM but update 1062 is listed in the \Updates folder) install it.
[The next step D is for customers using SQL Server or MSDE only! If you are using Pervasive.SQL, please skip to step E.]
D. Install Response For Windows (Database) to SQL Server or MSDE
1. Open “Programs \ CoLinear Systems” in the Windows Start Menu.
2. Choose “SQL Database Utility.”
3. Select the appropriate server type you are using.
4. Enter the Server Name, Login, and Password.
[NOTE: For MSDE, this information will be defaulted for you.]
5. Click “Login” to view the active R4W Databases. If R4W_Primary is not found it will ask you if you want to create it. Answer “Yes.” This will install the blank Response For Windows database to your MS SQL Server.
6. Multi-company systems: Enter the multi-company code listed on your license disk, and click the Exec Code button. This will add the extra databases required to support multiple divisions.
7. Exit.
[The next step is mandatory for conversions no matter which database engine you’re using.]
E. Register Database Drivers
1. Open to “Programs \ CoLinear Systems” in the Windows Start Menu.
2. Select the appropriate driver option you are using (Pervasive or SQL Server) and key the registration information supplied by the third-party driver vendor.
Congratulations! You’ve just installed Response for Windows!
F. Migrating Response Legacy data to Response For Windows
We strongly suggest you purge old data from Legacy Response system before starting the conversion in order to minimize the amount of data to convert and, consequently, the likelihood that you might experience some problems with that data. Please refer to the separate document “Purging Legacy Response Data” for more information.
**Extra Steps for Multi-Company Clients**
1. Most existing multi-company setups do not match our standard multi-company file layout. Therefore, please make a complete directory listing of your Response directory and send it to . That way we can give you instructions on how to make the standard R4W multi-company setup match your existing setup.
2. If you use Aristo for your manifest system
· Copy “…\Converison\Fix_Aristo_Order_No.vd7” to each division directory under the Response v5.2 directory.
· Run Fix_Aristo_Order_No.vd7 in each of the division directories.
· In the Conversion directory, rename “MapFile_Aristo.fil” to “MapFile_Aristo_OLD.fil”.
· In the Conversion directory, rename “MapFile_Aristo_MULTICOMPANY.fil” to “MapFile_Aristo.fil”.
Converting to SQL Server & MSDE
1. All clients must do a trial conversion first. When doing a trial conversion you should make a copy of the legacy Response directory and work from that copy. When you make the copy all users must be out of Response.
2. Insert the Response For Windows CD or download “RtoR4WConversion.zip” from the CoLinear FTP site. If you downloaded the conversion files, unzip them to “C:\Program Files\CoLinear\R4W\conversion”. If using the CD, please refer to the “conversion” directory on the CD.
3. If you are a Multi-Company Installation that uses Aristo for your manifesting please see the extra step above.
4. Run “…\Conversion\R4WConversion.vd7”
[where “…” is the drive letter of your CD-ROM or the R4W directory referenced above.]
5. Enter the full path to your live Response v5.2 directory or copy in the case of a trial conversion.
6. Click Convert to begin the conversion.
- Log in to the SQLServer using the “Mertech Database Drivers Automatic Login”. Enter the Server, User Name, and Password. IMPORTANT! Do not change the Database “default” entry. If using MSDE, accept the default User Name “sa” and the blank password as shown below.
When you’ve completed your entries click Connect.
NOTE: Depending on the size of your database and the speed of your workstation, this may take 24 hours or longer to complete.
After the conversion has completed, run the “Fix Orphans” program as described in Step G below.
This
space
intentionally
left
blank
Converting to Pervasive.SQL
1. Insert the Response For Windows CD or download “RtoR4WConversion.zip” from the CoLinear FTP site. If you downloaded the conversion files, unzip them to “C:\Program Files\CoLinear\R4W\conversion”. If using the CD, please refer to the “conversion” directory on the CD.
- Run “…\Conversion\R4Wconversion_PV.vd7”
[where “…” is the drive letter of your CD-ROM or the R4W directory referenced above.]
3. Enter the full path to your live Response v5.2 directory. The suggested Temporary Conversion directory and the Response For Windows directory should default, If not, change them to the desired locations.
- Click Convert to begin the conversion. Depending on the size of your database and the speed of your workstation, this may take 24 hours or longer to complete.
- After the conversion has completed, run the “Fix Orphans” program as described in Step below.
IMPORTANT! Response For Windows cannot be used until all orphaned records have been resolved. [2]
G. Locate and Tag Orphan Records
- Run “C:\Program Files\CoLinear\R4W\Data\FixOrph.vd7”.
- Select whether or not to log deleted orphans to a text file. Not logging deleted orphans will run faster however if for some reason you need to restore deleted orphans it will be much more difficult to do so. Unless you are really pressed for time we recommend that you leave this box checked.
- Click Okay to start the Report Mode.
OrphanFix_upd.log will automatically display so you can check to see what records have been marked for processing. Contact us for help if a large number of important records are tagged (such as order header or customer records). We recommend that you also run OrphRpt.VD7, which summarizes your orphaned records by filename/fieldname. This report provides actual record values so you can see the orphaned data that has caused these records to be so marked.
For example, say a lineitem record includes an Item_ID of WIDGET but no such record exists in the inventory master file (it’s related parent). That lineitem record is deemed to be an orphan because it is no longer associated with a valid record in inventory. Such records (orphans) cannot be saved in the SQL database environment because this violates the rules of relational integrity.
Which leaves us with two options: We can either (i) change that Item_ID to match an existing inventory master record, or (ii) delete the record entirely. The fix orphans program has been written to make intelligent decisions in this regard. In some cases a record will be deemed to be important (despite it’s orphaned nature) and will be changed to preserve its integrity. For instance, if a master media code contains an invalid list_code, that record will be changed. The now invalid list_code will be changed to read “[OBSO]” and a matching record called “[OBSO]” will be created in the list_code master file. OBSO is shorthand for Obsolete (field value). In this way, the contents of the master record are preserved while replacing the offending (missing) data with a neutral value that restores relational integrity to the database.
H. Post-Conversion Cleanup
After the Conversion and Resolution of Orphans is completed (R4WConversion.vd7 and FixOrph.vd7) there are several things to configure before you can use Response For Windows.