>We have a fox 2.o multi user application in a 5 user novell 5.0 network.When i execute a select statement with 20 fields using macro substituion for tables, i get the message "Temp file does not exist" when i run the sql for the second time using a different table. Once i log out and login the sql executes perfectly. whether i have to set the temp and sort directories in the local drive. Kindly help

I don't know if I understand your problem entirely but there is an issue with FoxPro's SELECT - SQL command that works something like this:

* Use SELECT - SQL for the first time:
SELECT ;
  * ;
  FROM SomeTable ;
  WHERE SomeCondition ;
  INTO CURSOR Temp1

* Run another SELECT - SQL, based on the results of the first one:
SELECT ;
  * ;
  FROM Temp1 ;
  WHERE SomeOtherCondition ;
  INTO CURSOR Temp2

The issue is that, if the first SELECT is on a single table and (especially) if the WHERE condition is Rushmore-optimizable on an existing index tag, in some cases FoxPro will not actually create the cursor as a disk file/cached in memory - instead it will create a filtered "view". If that happens, the second SELECT - SQL will fail because the temporary file that is supposed to hold the Temp1 cursor does not actually exist.

In VFP a NOFILTER clause can be added to the SELECT - SQL to force a disk file to be written:

* Use SELECT - SQL for the first time:
SELECT ;
  * ;
  FROM SomeTable ;
  WHERE SomeCondition ;
  INTO CURSOR Temp1 ;
  NOFILTER

However, the NOFILTER clause is not available in FoxPro 2.x, so we need to resort to one of several "tricks" to force the file to be written. One way is to create a "dummy" column in the result cursor:

* Use SELECT - SQL for the first time:
SELECT ;
  *, ;
  "X" AS Dummy ;
  FROM SomeTable ;
  WHERE SomeCondition ;
  INTO CURSOR Temp1

Again, I don't know if this is your situation but it's worth knowing about anyways.