Connecting to Pervasive SQL databases

From Sage Intelligence Knowledgebase
Jump to: navigation, search

Connecting to Pervasive SQL databases

When you upgrade your Pastel software you will need to point the Alchemex DB manager to the new company for the new database. You do this in the Alchemex DB Manager for Pervasive SQL, or in the Administrator or Report Manager.

Connecting to Pervasive SQL databases

To browse, add, modify, delete and troubleshoot Pervasive Database Names from within Alchemex use the “Database Name Manager for Pervasive.SQL” module from within the Administrator Tool or the Report Manager. To launch it drill down to the Pervasive ODBC Client Interface and right click PVS.SQL DB Manager. You will need to specify the server running the Pervasive.SQL engine (this is your local machine name if you are not running Pervasive.SQL Server version on a remote server). If the engine is on a remote server then you will need to supply a Username and Password for the server (the user name needs administrative operating system privileges on the server machine). An Alchemex Explorer style module will open which allows you to browse, add, edit, delete and troubleshoot Database Names under the control of the Pervasive.SQL engine. The module is shown below.

Connecting to Pervasive SQL databases

Connecting to Pastel Version 6.0 and Later

Assuming Alchemex and Pastel are successfully installed and functioning on the workstation then you will need to create a Pervasive Database Name to reference your data files on either the local Machine (running a Workgroup or Workstation Engine) or on a Server Machine Running Pervasive.SQL 2000. In Alchemex version and later this can all be done from within the Alchemex Administrator Tool.
Run the Alchemex Administrator program (Windows > Start > Programs > Alchemex > Administrator). In the Object window on the left hand side double click on Alchemex Enterprise. From the list of Connection Types that appear double click on Pervasive ODBC Client Interface. A connection called Pastel 60 (or the relevant version) should appear. Click on it and on the right hand side property pane change the Server Name property to the name of the machine running the Pervasive Engine (leave it black if each machine is to use its own local engine) and then press Apply button on the top right of the Property pane. For the Database Name property click the button on the right to retrieve a list of existing Database Names that are under the control of the 

Pervasive Engine. Either choose one of the existing ones or select the [Create New Pervasive DB Name] option. If you choose to add a new one then you will be prompted to browse to the location of your Pastel DDF files and then to your DAT files. The DDF Path is the folder in which the Pastel Data Definition Files reside (for a local installation this is usually in C:\PAS60 (or relevant version) but for a server installation is usually in the chosen Pastel root directory). The DAT files are your Companys physical data files and the folder is usually one directory below the Pastel root directory and is usually named with the same name as the Pastel Company (e.g. C:\PAS60\WOO2003 for the 2003 set of Accounts for the company Woodridge (PTY) LTD). Note that if you are using a remote Pervasive SQL Server then you will be prompted for logon details. You will need a logon with administrative privileges on the server to perform the functions.

To test that the connection is valid and functioning correctly, select it (Pastel 60) in the object window and choose Check/Test. If a Connection [Pastel 60] Check Succeeded message appears then the connection is functioning correctly. It does not necessarily mean that the Data File Path is accessible. As a final check to see that the Data File Path is accessible, double click on Pastel 60 in the Object window. A number of Data Containers should appear. Select LedgerMaster (P60) and right click and choose Check/Test. If a Container [LedgerMaster (P60)] Check Succeeded message appears then the Data File Path is accessible and you are ready to run reports from the Alchemex Report manager.
It is important to understand the components involved in this connection. The layers are:- 

1. Alchemex Pervasive ODBC Client Interface Layer (local) 2. Pervasive ODBC Engine Interface layer (local or remote) Pervasive SQL Engine 3. Pervasive SQL Engine (Workgroup/Workstation/Server) Pervasive Database Name 4. Pervasive Database Name Pastel DDF and DAT files

All of the necessary components must be functioning correctly for a successful connection. To trouble shoot any of the components we recommend doing the following:- 
To verify that your Pervasive SQL Engine is working correctly run Pastel and see that you can view data for the chosen Company. 

1. To verify that your Pervasive ODBC Engine & Client Interfaces are functioning open the Pervasive Control Centre and drill down to the machine running the Pervasive SQL Engine. Open the Databases node, then open a Database (DEMODATA if it exists) and then the Tables node. In the right hand pane select a Table and right click and press Open. If data appears then the ODBC layers are functioning.

2. To verify that the paths you have set for your Database Name are correct select your Patel 60 connection and then choose Tools > PVS.SQL DB Manger. Open the server and select the relevant Database Name. Note down the DDF and DAT paths in the property pane and verify that these paths are valid and accessible from the point of view of the machine running the Pervasive SQL Engine (you may need to get onto the Server machine to verify this). Using windows explorer you should see DDF fiels in the DDF folder (e.g fileds.ddf and files.ddf). In the DAT file folder you should see a number of DAT files (e.g. ACCTRN.DAT).