ODBC error

[vbajock]

Users are getting an error on large ODBC queries which use an attached P-SQL 2000i table. The error says:

[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)

After a little investigation I discovered that a query had gone awry, the user had rebooted and this left a 1 gig dead swap file on the boot drive of my 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.
Does anyone know anything about how to set this ? Does the ODBC.ini file on the server actually control this?
Thanks

 

[TomKane]

I'm not sure how to get at it in 2000 put in SQL 7 it's referrd to as the external sort file directory. One possible work around might be to take the 'order by' out of the sql statement.

 

[vbajock]

My real problem is where the sort files are landing. I do not want them on my boot drive, because it results in a possibility that a query gone awry can run my boot disk out of disk space, so I am asking if any one has any idea on how to change the location of the external sort file directory, so I can point it to a bigger disk.

 

[TomKane]

In P.SQL7 there is a setup utility that allows you to configure the server and there is an external sort file directory. My best guess for you would be to look at the Pervasive Control Centre and see can you make the change in there.

Best of luck

 

[vbajock]

I looked in the Control Center. There is a folder called "Directories". It has setting that look like they might be for the accounting app. Their is a "Working Directory" that is set to "Default", and I can change the directory name here. I have a DB Names directory, set to C:\Winnt. If any one know the affect of changing one of these settings, please let me know.
Thanks

 

[Vancouverite]

The DB Names file directory contains the DBNAMES.CFG file. This tracks the names and locations of your databases for ODBC connectivity. If you change this directory, be sure to move the DBNAMES.CFG file to the new location.

The Working directory controls where temp files are stored. Normally, this should be the same as the location of the DDF files or the data file. However, you can set it to a fixed location to help speed things up, or to provice more data space, etc.

 

[vbajock]

Sounds like Working Directory is it. Do you know if changing this to a different directory would affect the accounting system (Macola) that is using the PSL engine in any way?