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."
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
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.
PerfishentAuthor 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 GuruCommented:
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.
PerfishentAuthor 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.
Bill BachPresident and Btrieve GuruCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.