Solved

ODBC Pervasive Database Connection

Posted on 2014-10-22
8
1,123 Views
Last Modified: 2014-11-26
Hi Guys,

I am trying to setup an ODBC database connection,

I've downloaded and installed the Pervasive Workgroup Engine from Gold Star Software.
In the Windows tray, the Pervasive Engine is running.
In ODBC data source, I added a new data source, and selected the Pervasive ODBC Client Interface.

Data source name = "Test"
Description = "Pervasive ODBC Client Interface"
Server Name = "Local host"
Transport Hint = "Try TCP/IP first .."
Data Options = Database Name

Upon clicking "Create" or "Get List", I am receiving the following error message:
"Cannot connect to Pervasive.SQL Server Engine, Workgroup Engine, or Workstation Engine.  Make sure either the Pervasive.SQL server is started, or Pervasive.SQL Workstation Engine is installed."
0
Comment
Question by:Rupert Eghardt
  • 3
  • 3
8 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 40396915
The Server Name should be "localhost" (all one word) not "Local Host" (two words).  Localhost is the name of the local machine.  If you were connecting to a remote machine you would use that machine name.
0
 

Author Comment

by:Rupert Eghardt
ID: 40397013
Sorry the "localhost" is "localhost" by default, I just typed like that.

Still the same error message?
Pervasive.jpg
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40397049
There are a few different pieces to the puzzle.  I know that you have indicated above that you installed the PSQL v11 Workgroup Engine.  However, another thread implied that you may have installed the PSQL v11 Client instead.  The Client does not contain a full engine, but rather only the Client components to talk to the engine. (The Client does contain the "Client Cache Engine", or CCE, which WILL show up in the system tray, but this is not a complete standalone engine -- it caches data from a remote server.) Please re-verify exactly what was installed.

If you are intending to access local database files on your own computer, then you need the PSQL v11 Workgroup Engine installed and licensed (beyond the 30-day trial).  In this case, remove the PSQL Client, Install the PSQL v11 Workgroup, and your configuration (above) should work just fine.

However, if you are accessing database files on a remote server, then the Client is the correct piece to have installed.  In this case, change the configuration for Server Name to the name of the remote server, and verify that there are no firewalls blocking access to the engine (either client-side or server-side). You can also test remote access from the Pervasive Control Center by right-clicking Engines and selecting New/Server.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Rupert Eghardt
ID: 40398281
Thank you Billbach.

I downloaded the v11 Workgroup engine and is working.
I managed to setup a new "test" database, and was able to connect in ODBC.

I am however experiencing the following problems;

1.  If I right-click on databases and select "new database", enter a database name and specify a "test location", it asks me for a username & password.  No matter what I insert, it doesn't take it.  I've tried Master, (no psw), etc.
* I've added a screen shot

2.  When I right-click on the Engine, logout Anonymous, log back in as Anonymous, the "test" database then only shows in the list

3.  As a test, I've deleted all databases, but when I right-click databases and try to add a new database for example:  "demodata", it tells me that it already exists.  In the event of an existing database, such as "DemoData" ...
How to I open an existing database, instead of creating new?

4.  I managed to add a Pervasive 2000i database that I was trying to open, by specifying the DDF files location, as well as the data path.  The tables are all showing, but trying to view records in a table, it says "Invalid User Authorization Specification"
Is this a user account specific to the database I am trying to open?
Pervasive2.png
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 40400398
There are two parts to any database.  The first part is a "Named Database", which is an entry in the database file DBNAMES.CFG.  This is what the PCC is showing you in the tree on the left side.  The second part is an (optional) ODBC Engine Interface DSN, which is created by default for 32-bit application use.  You will NOT see this in the PCC, but will ONLY see it if you run the 32-bit ODBC Administrator (from Tools menu).  The errors you are showing are common for limited-access users, who do not have proper access to the ODBC Administrator.  

So, we now need to fix it.  First, log in as a true administrator, start the PCC, and then run the 32-bit ODBC Administrator from the Tools menu.  Once there, go to the System DSN tab, find any of the PSQL DSN's and remove them.  If you get an error doing this, then you are not a true administrator, and you may need to disable UAC first.

Once these old DSN's are removed, and assuming the DBNAMES.CFG is also empty (i.e. there are no databases in the PCC), you can create a new one.  Again, make sure you are Administrator first, then create the new database.  If you get the same problem, try unchecking the "Create 32-bit DSN" checkbox and see if that works.  If it does, then you'll have to MANUALLY create the 32-bit ODBC DSN's in the ODBC Administrator.  Not a show stopper, but painful to have to use two steps.

Alternatively, if a DSN like DEMODATA already exists and you just deleted the Named Database, right-click to create the new database, give it the name Demodata, and then uncheck the box to create the DSN (which will fail if it already exists).

Let's get this all working first, and let's get access to DEMODATA once again as a stepping stone.  Once we have the engine working correctly, we can move on to your last issue.
0
 

Author Comment

by:Rupert Eghardt
ID: 40405055
Thank you Billbach,

First, log in as a true administrator, start the PCC, and then run the 32-bit ODBC Administrator from the Tools menu.  Once there, go to the System DSN tab, find any of the PSQL DSN's and remove them.  If you get an error doing this, then you are not a true administrator, and you may need to disable UAC first.


I removed all the PSQL DSN's without an error

Once these old DSN's are removed, and assuming the DBNAMES.CFG is also empty (i.e. there are no databases in the PCC), you can create a new one.  Again, make sure you are Administrator first, then create the new database.  If you get the same problem, try unchecking the "Create 32-bit DSN" checkbox and see if that works.  If it does, then you'll have to MANUALLY create the 32-bit ODBC DSN's in the ODBC Administrator.  Not a show stopper, but painful to have to use two steps.


I don't know how to check the contents of the DBNAMES.CFG  ...
I tried adding a new database, but still received the login username / password prompt (even with unchecking the "create 32-bit DSN" option.

Alternatively, if a DSN like DEMODATA already exists and you just deleted the Named Database, right-click to create the new database, give it the name Demodata, and then uncheck the box to create the DSN (which will fail if it already exists).


I still couldn't delete the problematic database.


I uninstalled PSQL Workgroup, reinstalled, and only then was I able to add a new database.
The default databases were now also listed after the reinstall.

I am still unable to delete the problematic database,
7004:  General failure code (even with "run as administrator")
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40406412
Without screenshots or more detailed information, this is very hard to troubleshoot.  Again, a database is stored in TWO pieces, the DBName and the DSN.  If you see it in the Pervasive Control Center, then you are seeing the DBName.  If you see it in the ODBC Administrator, then you are seeing the DSN.  Which one are you trying to remove?

In the worst case, you can always remove everything by uninstalling the WGE, then deleting the DBNAMES.CFG file (left over in the C:\ProgramData\Pervasive Software directory), and then reinstall the Workgroup Engine.  All should be back to normal after a perfectly clean install.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
This article describes some very basic things about SQL Server filegroups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now