MKSoftware HOme
Grey Rule
MK Software MK Software
Blue Rule
 
spacer
     
Spacer Spacer Spacer
MK PowerTools SQL for BusinessVision Frequently Asked Questions:

 



Q: I have moved my database location, how do I restore my connection in PowerTools?

A: PowerTools uses Pervasive ODBC for the data access connection.  If you have changed the path of your database use the following steps to restore your connection.

Use this method for BusinessVision Client-Server SQL Edition.

  1. Open MK PowerTools and determine the DSN (Data Source Name) that your company connection is using (example MKPT001).  After selecting the location you should see the DSN in the lower part of the screen.  Write down the name.
  2. Go to Administrative Tools and open Data Sources (ODBC), select the "System DSN" tab and locate the DSN as per step 1 and select "Configure".
  3. The "Pervasive ODBC Client DSN Setup" form should open and under the "Server" section you should see "Address" (This is your Database Server's name or IP address), edit it if it has changed.  Below that you should see the Server "Data Source Name" dialogue with a pick list control.  In the pick list control your should see the Server DSN which is likely the same name as the Client DSN (but does not have to be) and write the name down as it will be required in the next steps.  Testing your connection to the Pervasive Server can be done by selecting the "Get DSN List" button, this should load the list of defined DSNs on the Pervasive NT Server and you can see them in the pick list.  If you get any errors at this point you must troubleshoot your Pervasive installations or networking.  The Test button will not work yet as the connection is still broken, if you want you can go back and try it again later once the Server DSN is repaired.
  4. Next go to the Pervasive NT Server computer.  The remaining steps are the same as the next article on managing a Pervasive Engine DSN with one exception.  On the Server Engine, the path to your BusinessVision data folder must be the Physical Path to your BusinessVision data files and NOT the logical path or mapped drive path.  If you do not understand what the difference is, we highly recommend you contact a qualified networking specialist and have them do the setup.
  5. Once the Server DSN is configured correctly, and the Client DSN can connect to the Server DSN, everything should be working again.

Use this method for BusinessVision Standard Edition, Small Business and Limited Edition or for completing the Client-Server section as per the above steps.  The following section explains how to maintain your Pervasive.SQL Engine ODBC DSN.

  1. Open MK PowerTools and determine the DSN (Data Source Name) that your company connection is using (example MKPT001).  After selecting the location you should see the DSN in the lower part of the screen.  Write down the name.
  2. Open the Pervasive Control Centre, expand the branch to your workstation name, then databases and then right-click on the database and select properties.  Write down the the DBName.
  3. Right-click on the Configuration object and select "Maintain Named Databases".   and  Delete the Pervasive Workgroup Engine shortcut from the Start Menu / Programs /
    Start-up folder.  On the next screen your should see a list of "Registered DB Names" under the green barrel icons.  Locate the associated DB name from step 2.
  4. After selecting the DB Name, you should see the path settings on the right side.  The Data File Location should be the exact path to your BusinessVision data files.  The Dictionary Path should be a combination of the path to your database files with \mks appended to the end of the string.  For example the data path could be F:\bvdata and the dictionary path would be F:\bvdata\mks.  Edit the paths and click OK and save the settings.
  5. Now we will test the ODBC connection.  Go back up to the Databases container and expand it and drill down to the Database name and then select "Tables".  You should see several dozen tables on the right including ARTRANS, INVFULL, etc.  At this point if you double-click on one of those tables, the data manager window should open and you should be seeing your data in raw format.
  6. If you get any errors, you must fix your Pervasive connection or installation.  Try first to verify the settings, next delete the database and re-create it and the final solution is a complete removal and reinstallation of the Pervasive Workgroup engine (see another faq article on how to do that).
  7. If the ODBC connection is now working, go back to MK PowerTools and select the company connection and go to Properties.  Edit the path to the BusinessVision database.  Save the setting and everything should be working again.

[back to top]



Q: How do I install PowerTools on Microsoft Terminal Server and/or Citrix?


A: The following applies to all Terminal Server platforms, from NT4 to 2003 server. Terminal
Server and Citrix are the same so the language in this document is interchangeable.
You must follow these instructions 100% otherwise your setup will fail 100% of the time and
PowerTools will never work.


Firstly, you must perform both the installation and setup the connection to your
BusinessVision company on the PHYSICAL CONSOLE of the Citrix Server. The PHYSICAL
CONSOLE means you are sitting in-front of the monitor, keyboard and mouse that is
connected to the server. If you are not able to connect to the Physical Console, don't bother
going further**. If you are logging in through a Terminal, Citrix or Remote Desktop**
connection this DOES NOT WORK EVEN IF YOU ARE LOGGED IN WITH THE ADMINISTRATOR
ACCOUNT! (This is not a PowerTools limitation but a Pervasive.SQL problem). Once you have
successfully connected MK PowerTools to your BusinessVision database, users can then login
and run PowerTools through virtual sessions.


Secondly, if your Windows users are part of the local Windows "Users" group then you must
give the Windows local users group "change permissions" on the PowerTools program folder,
sub-folders and files, usually found in C:\Program Files\MK PowerTools. Consult Microsoft help
on setting file and folder permissions.


**TIP: With Windows 2003 Server, you are able to shadow the physical desktop session of
the server as if you were sitting at the console. You must use either another Windows 2003
Server's Remote Administrator program with the console switch checked or use Microsoft
Remote Desktop executed from a command line. To use Remote Desktop or RDP, create the
connection and save it to "c:\server.rdp", then from the command line or batch file execute
the following string without the "". "mstsc c:\server.rdp -console". The monitor on the Server
will display CTRL ALT DEL while the remote is using the console session and you can
disconnect and swap back and forth.

 



Q: How to create a Pervasive.SQL Server Engine DSN (for BV CSE)?


A: Perform this on the database Server if you are using BusinessVision Client-Server Edition and you are having errors using the PowerTools SQL automatic Server DSN creation function.

  1. On the Server, open the Pervasive Control Centre from the Start Menu / Programs / Pervasive folder.
  2. Drill-down to Databases container, right-click on the Databases container and select "New
    Database..." from the menu.
  3. Enter a name for this database, keep it less than 8 characters and no spaces (eg: mkabc)
  4. Browse to the "Physical Path" to your BV data folder. NOTE: The physical path is the hard-wired path, so if your server has a hard-disk drive identified as E:\ then the physical path would be similar to (e:\bvdata\abc).  DO NOT use the logical path or mapped drive path.
  5. Check the "Use advanced settings" checkbox option and click next
  6. On the next Window you will see "Description", "DSN Type", "DSN Open Mode" and
    "Database name", leave those values at default and click the "Create..." button on the lower right side.
  7. The next window should open with two paths showing, "Dictionary location:" and "Data file
    locations:" along with Database name at the top. Click on the end of the "Dictionary location:" string and append "\mks" to the path (Do not type in the ""). So the dictionary path should look like e:/bvdata/abc/mks and the data path e:\bvdata\abc and the
  8. Click Next, Finish and Close.
  9. If you tried already to create a DSN or your had PowerTools setup at some point, there are likely several files with a .ddf extension in the mks directory.  If so then you are ready to test if you can browse the data. If not, copy the DDF's from the PowerTools Program \ Support \ Version folder into the mks directory.
  10. To test it, go back in Pervasive Control Centre, expand the database you just created and click on tables.  You should see several dozen tables on the right side.  If you double-click on any of the tables that should have data in them you should see data in the grid and everything should be working ok.  If you get an error, something is wrong with your Pervasive Database setup or installation.  PowerTools requires a functioning database system and ODBC connection in order to operate.  See www.pervasive.com for more information.


Finally, go back to your PC and MK PowerTools SQL should be able to detect the Server DSN and setup your connection automatically.

[back to top]



Q: How can I optimize my BusinessVision Client-Server SQL environment?

A: BusinessVision Client-Server Edition includes a robust Pervasive.SQL 2000 or 8.7 Database
Server Engine which means that you don't need to be running database servers on any of your
workstation PCs (Pervasive Workgroup Engine). Only one computer should be running the
database engine and that is your file server. Do this and you won't have gateway delegation
issues and your network and computers will run faster. In a Client-Server database
environment all the workstation PCs require is the Pervasive client requesters to access data
on any remote (server) engine. The Pervasive client requesters are installed by default with
any Pervasive.SQL installation. So the objective is to install the Pervasive.SQL Workgroup
Engine as required by BusinessVision, then disable the loading of the Workgroup engine on the
PC.

Here are the simple steps to disable the Pervasive Workgroup Engine:

  1. Delete the Pervasive Workgroup Engine shortcut from the Start Menu / Programs /
    Start-up folder.
  2. Open the Pervasive Control Centre, drill down the tree view until you see
    YourComputerName / Configuration / Client / Access. Set the "Use local Microkernel
    Engine" to OFF.

These are the minor issues you might run into.

  1. With the local engine turned off, you will not be able to read data on your local
    workstation, the solution is to move it to the server!
  2. If you edit Crystal Reports, you will need Btrieve access to the C:/Model folder on your
    local machine. So enable the engine and turn it off when you are done.
  3. Some poorly designed programs require database access not only to your
    BusinessVision data directory but their own local program directory. You can solve
    this by installing the program to a directory path located on the server.

[back to top]



Q: Does Inventory Code Change or Merge modify my sales history or Bill of materials?

A: Yes, PowerTools modifies all current and historic databases including Sales Orders, PO, BOM,
Sales History, Inventory Receipts. You system works as if you have been using the new part
number since day 1.

[back to top]



Q: Will the Un-issue a Purchase Order module un-issue a PO that has been fully or partially received?

A: Yes, PowerTools will allow you to make changes to POs that have partial or full receipt of some or all items. All information is retained with the Purchase Order when it is re-issued.

[back to top]



Q: How does PowerTools SQL access my BusinessVision database?

A: PowerTools connects to your BusinessVision database utilizing the Pervasive.SQL relational
interface or otherwise known as ODBC (Open Database Connectivity).
ODBC is a API (Application Program Interface) standard used to communicate with a
Relational Databases such as Microsoft SQL Server, Informix, IBM, Oracle or Pervasive.SQL to
name a few.


SQL (Structured Query Language) is a high level database language used to talk to a
Relational Database. MK PowerTools SQL leverages this high-level language whereas
BusinessVision uses a lower-level Btrieve API.


There are 2 key reasons why using the SQL interface is the best.

  1. Database Integrity. SQL features automatic transaction roll-back, this way if any
    PowerTools process does not complete from start to finish, terminates unexpectedly,
    power failure, database issue, whatever; all transactions are automatically rolled-back
    and no information what-so-ever is modified in your database. You will never have
    partially completed entries or processes.
  2. Superior Database Performance. When used in conjunction with BusinessVision Client-
    Server SQL Edition, PowerTools leverages the performance benefits of the
    Pervasive.SQL Server Engine. Through SQL, PowerTools talks directly to the database
    server and most of the database queries and operations actually run on your server.
    Since the database is on the same computer (rather than over a network), database
    performance is significantly faster and more reliable.
MK PowerTools requires that all workstation be upgraded to Pervasive Workgroup or NT Server
Service Pack 4, available for download on our support page.

[back to top]



Q: Can PowerTools Import Inventory Pricing?

A: Yes, Warehouse Replicator includes a feature called Remote Warehouse Replication that allows you to safely replicate part numbers from one BusinessVision system to another separate
BusinessVision system. Here's how it works...
First you setup a shell or backup database, something other than your live database. Import
your information into the backup database and check it in BV. If you are satisfied you have
achieved the desired result, you can safely replicate the items into your live database using
Warehouse Replicator (Including Workbook data!!). You can even distribute the items to
several warehouses at the same time.

[back to top]



Q: How do I upgrade my BusinessVision system to Pervasive.SQL 2000i or 8.7?

A: Simply run the Pervasive Workgroup Engine installation included on your BusinessVision CD
under the folder PVSW, then install Pervasive Workgroup Engine Service Pack 3 and 4 (Check
our support page). For version 7 installations, simply run the install in the PVSW folder.
Do the installation on the Server first, then the workstations.

[back to top]



Q: Why should I use Pervasive.SQL 2000i or 8.7?

A: The Btrieve database architecture does not employ a highly structured method for database
access. Unstructured access means that each workstation independently accesses the
databases in the process of posting invoices, journal entries, reports, etc. Data integrity is
preserved by the operating systems and network by employing a first-in and file locking
model. With more users and more transactions, frequent collisions occur between multiple
users accessing the database, sometimes causes conflicts, lock-ups and errors in both the
application (BV32) or the database.


The Pervasive.SQL database architecture uses a Client and Server type methodology whereby
database requests (Client Requests) are managed through a single transaction engine
component (usually running on the server). The transaction engine acts as a gateway to the
database, delegating and processing all traffic in and out. This establishes control and more
fluid and reliable operation resulting in fewer errors, timeouts and better performance overall.

[back to top]



Q: Do I have to upgrade all stations to use Pervasive?

A: Yes, if you want concurrent access to data. Btrieve 6.15 and Pervasive can not access the
same information at the same time.

[back to top]



Q: Can I run PowerTools while other users are logged into BusinessVision?

A: Yes and No, the Search and Replace Code Change and Merge tools will lock some files
during the process. G/L Account Code Change and Merge will definitely lock the
BusinessVision Control File preventing any user from processing transactions, logging on, etc.
For these programs, it is best to queue your change list and run it after hours when you go
home at day end.
Most other PowerTools programs can run concurrently with BusinessVision.

[back to top]



Q: What are the system requirements for running PowerTools SQL?

A: BusinessVision version 5.x or later, Pentium II or better, minimum of 64MB of RAM, 50MB
of free disk space, Pervasive.SQL 2000i sp4 or 8.7. Note: the Pervasive.SQL Workstation is
not supported.

[back to top]



Q: Do I need to convert my files to use Pervasive.SQL 2000i or 8.7?

A: No conversion to data files is required, the upgrade is seamless.

[back to top]



Q: MK PowerTools SQL requires that I upgrade my Btrieve database system to Pervasive.SQL 2000i service pack level 4 or Pervasive 8.7, how do I do this?

A: To run MK PowerTools SQL you must upgrade your Btrieve database system to Pervasive.SQL
2000i service pack level 4 or Pervasive 8.7. The advantage of upgrading your system to 8.7 is
that you do not have to apply any service packs and faster performance.

Pervasive.SQL 2000 SP2 and Pervasive 8.7
The Pervasive installation is included on your BusinessVision CD under the directory PVSW.
Pervasive.SQL 2000i SP3 Server and Workgroup Engine Service Pack 3 must be downloaded from the Pervasive Website at: http://www.pervasive.com/support/updates/2000i_sp3.asp
You will be required to register and login to Pervasive Website to download sp3.

Pervasive.SQL 2000i
Workgroup Engine Service Pack 3
http://www.mksoftware.com/download/PVSW2000WGESP3.zip
Workgroup Engine Service Pack 4
ftp://ftp.pervasive.com/downloads/PervasiveSQL2000i/Updates/SP4/PSQL2Ki_SP4_WGE.exe

Server Engine Service Pack 4 - (BV Client-Server Edition)
http://www.mksoftware.com/download/PVSW2000NTSP3.zip
Server Engine Service Pack 4 - (BV Client-Server Edition)
ftp://ftp.pervasive.com/downloads/PervasiveSQL2000i/Updates/SP4/PSQL2Ki_SP4_NT. exe


The updates should be applied to all systems and installed in order, service pack 3 (reboot) then
service pack 4.


Users of BusinessVision Client-Server edition need to download and install both the Server
and Workgroup service packs. Users of BusinessVision Standard, SBE or LE only require the
Workgroup Engine service packs.


[back to top]



Q: How do I completely remove Pervasive.SQL 2000 / 8.x from my system?

Use this procedure if you are upgrading from Pervasive 2000, or if you have Pervasive.SQL
Workstation installed, or if you are having problems with Pervasive that cannot be resolved,
or if you are or if instructed to do so by technical support:

  1. Login to your computer with Administrator permissions
  2. Shut-down any Pervasive Engine running on the local computer.
  3. Run the Microsoft ODBC Administrator (For Windows 98 and NT it is in the Control
    Panel, for Windows 2000/3 and XP it is called "Data Sources ODBC" and it is in
    Administrative Tools)
  4. IN ODBC Administrator, go to the System tab and write down the names of every
    Pervasive ODBC Connections, write down if it is a Client or Engine DSN and document
    the name and properties, the ODBC name is case-sensitive. For Engine DSNs, rightclick
    on Configuration and Select "Maintain Named Databases" and document the paths
    used by the Databases. Some ODBC connections maybe need to be manually recreated
    once you re-install Pervasive. The Pervasive "upgrade" will NOT upgrade your
    DSNs, so this is mandatory if you want MK PowerTools to continue working.
  5. After documenting, remove each System DSN, using the remove button and close the
    ODBC Administrator.
  6. Go to Control Panel, Add/Remove Programs and uninstall any Pervasive Software listed
    in reverse order, so first remove Sp4. Next, Sp3 will likely need to be reinstalled before it
    can be removed, that should work. If prompted to remove license keys and settings, say
    yes.
  7. If you have "Data Access for BusinessVision" installed, remove that too as it is replaced
    by the newer Pervasive ODBC. If you have any programs that use the older ODBC, then
    contact those vendors to upgrade your DSN.
  8. If not prompted to do so by the Un-installer, then restart your computer anyway.
  9. Delete the Pervasive program folder on your local hard disk, this is usually at C:\PVSW
    and the folder C:\PVSWARCH.
  10. Run Registry Editor by going to the Start Menu, Run and type regedit, then press enter or
    OK.
  11. Save and then Delete the registry key
    "HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software". See Microsoft
    documentation on saving / backing up registry keys.
  12. Reboot and re-install Pervasive from your BusinessVision CD-ROM under the directory
    PVSW. It is also advisable to read the BusinessVision Installation Manual for more info
    on implementing Pervasive.SQL.
  13. If using Pervasive.SQL 2000, reinstall Pervasive Service Pack 3 and then 4.

[back to top]


 

 

.

.