Avatar of Perfishent
Perfishent
Flag for United States of America asked on

Pervasive SQL database suddenly stopped working

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."
DatabasesMicrosoft 365VBA

Avatar of undefined
Last Comment
Bill Bach

8/22/2022 - Mon
Bill Bach

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.
Perfishent

ASKER
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 Bach

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Perfishent

ASKER
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.
ASKER CERTIFIED SOLUTION
Bill Bach

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.