Pervasive SQL database suddenly stopped working

Perfishent
Perfishent used Ask the Experts™
on
Our Pervasive SQL database suddenly stopped working. I can't connect with PCC although I can log on to the Pervasive server and can see that the relevant processes are running. I've restarted the server and the processes.

I get the following error in Access VBA when I try to connect programmatically:

"The ODBC client Interface cannot access the datasource because  SQL connection manager is not running  at the specified port number."
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill BachPresident and Btrieve Guru

Commented:
Can you run the PCC and access the database directly on the server?  I have seen cases where Windows Updates take effect which enable the firewall on a server, blocking out the database engine from accessing the network.  If the PCC works on the Server and can access the database (Try DEMODATA if needed), then try the same thing from a client workstation.  If that fails, then check the firewall status and disable, if needed.  Or, poke a hole for TCP port 1583, and it should allow for ODBC traffic to the server again.  While you're on the server, run "NETSTAT -a" and confirm that the TCP port 1583 is in a LISTENING state.

Another issue could be DNS on the environment.  The clients resolve the server name through DNS, and if DNS has suddenly gone wonky, then it won't work, either.  Try to PING the server by name and make sure that the right address comes back.  If not, fix the DNS or add an entry in the HOSTS file.  While you're there on the client, try telnetting to the server on port 1583 -- you should get a black screen with no error if the TCP port is open.  If you get an error, then you likely still have a firewall issue.

Author

Commented:
Actually, I discovered that it was a dynamic query generated by VBA that I was running out of MS Access. A data sanitizing function I was using dropped the commas from a WHERE IN clause that contained about 30 numeric id's. The IN clause ended up with one long number instead of a comma-delimited set of numbers. Pervasive did not like that for some reason and croaked :-).
Bill BachPresident and Btrieve Guru

Commented:
How long was the number, and what was the data type?  Can you give me the query clausde that caused the engine to crash?  It is my opinion that any query, no matter how bad, should NEVER cause an engine crash.  I can see if this same issue exists on the current v12 release and get this to the engineers to correct it, if needed.

Author

Commented:
We're running PSQL 11.2. I don't have a copy of the query as it is assembled at run time. It is a simple SELECT query though, except the WHERE IN clause was passed a single number 312 digits long rather than 52 numbers 6 digits each separated by commas. The query was filtering on a numeric, indexed column that is not the primary key.
President and Btrieve Guru
Commented:
OK -- thanks for the clarification.  I tested this on PSQL v12.01, and it had no problems with 312 digits on "SELECT * FROM PERSON WHERE ID IN (...)", where ID is a BIGINT field.  I also tested it out to 600 digits with no errors.  So, I believe this may just be an anomaly in v11.2, and that it as already been fixed.

Note that PSQL v11.30 (Service Pack 3) was released in January 2013, and that there have been 23 updates to this SP3 release (v11.31) over the last 2.5 years.  Applying the free patches may help address other issues that have also since been fixed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial