ODBC Swap file settings

[vbajock]

A user used Access to query a huge file and rebooted her computer while the query was still in progress. All users started getting an error:

[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface] Out of Disk space writing temporary sort file. The tempfileDirectory = variable in odbc.ini can be used to specify different drive/directories". (#0)

which essentially shut down any ODBC activity at this client's site.

After a little investigation I discovered that the query that had gone awry left a 1 gig dead swap file on the boot drive of the app server in the Winnt directory. I looked at the Pervasive Console and could not find anywhere to set the swap file directory to a different drive, and there is no info on this that I can find on Pervasive's web site or on the net. I posted this same question on the BTrieve forum and they don't have an answer. Does anyone know anything about how to set the directory location for ODBC swap files ? Changing settings in ODBC.ini don't do jack. Macola says there support for ODBC ends at making the drivers work, after that, your on your own.

Thanks

 

[chadt]

have you bounced the apps server?

 

[MacolaHelp]

vbajock: Actually, I've had a lot of "fun" lately w/CPU utilitization > 100% & moving swap files, etc., that may be Macola related or odbc app related. If you want to discuss more, I have a lot of things we've tried, some w/ & some w/o success. With your expertise on the programming side, maybe we could put it to rest once & for all, since I've been talking w/pervasive & macola about it for months without resolution. In all fairness, Patrick @ Macola did suggest the swap file move which may have actually truly solved the problem. For a while, I thought it might be odbc apps such as commission calc or crystal causing our utilitization problem. However, once I cleaned up the C drive on our server & moved the swap to D w/plenty of room to grow, I have only experienced the problem once since mid January. There are some default settings in PCC that you could direct to another drive than c:\winnt. You might want to try d:\ something if you have more room there. Good luck & let me know if I can help.

 

[vbajock]

Ended up talking to Pervasive. Turned out I had three free support calls because I signed up for their developers program about 6 months ago. First they gave me a bunch of BS saying it was access creating the swap file. After I pointed out a few facts that made even them see that this was impossible, I got kicked to someone who knew something. The Pervasive 2000i Console, Configurations, Directory has a Working Directory property page (if you can call it that) is where you can set the swap file location. ODBC queries generated from clients are swapped into here before the data is transmitted to the client app.

I suspected that the Working Directory being blank was it, but I didn't want to set it without knowing whether resetting it would affect the SAS shipping system and Macola running against this engine. Their answer was, it doesn't. Why anyone would have this setting default to the WINNT directory is beyond me. That is just plain stupid. It sets up the server's boot drive to run out of disk space. That's really smart. This setting should be in the install program. The error message that gives you the wrong solution is also a nice touch. No wonder MS SQL is kicking their butt.