00523: Error 77 or FSERR=5, FSERRS=-21 executing an SQL SELECT with an ORDER BY or GROUP BY clause

Title:

Error 77 or FSERR=5, FSERRS=-21 executing an SQL SELECT with an ORDER BY or GROUP BY clause

Description:

When executing an SQL statement that contains an ORDER BY or GROUP BYclause, an error similar to the following may result with the ODBC Driver: 

State=S1000][Error=77][BASIS] [BASIS ODBC Driver] Execute() file system error:                                    fserr=5 fserrs=-21 

Additionally, the A: drive may be accessed. In Pro5 and VPro5, an SQL Statement using the ORDER BY clause may return an Error 77 – General SQL error. Printing the sqlerr(sqlchn) will allow you to get more information on the error. 

If the error goes away when the SORT BY or GROUP BY clause is removed, then it indicates that the ODBC Driver or BBx didn’t have a valid place to create a temporary mkeyed file. This file is used to temporary store the selected records so that they can be read back to the application in sorted order. 

In order to ensure that the temporary file can be created, an environment variable named TMPDIR must be set. This should be set to point to a directory that the BASIS product can use to create temporary files. When setting this variable, ensure that the device containing the directory has enough space left, and that the BBx or Data Server process will have sufficient rights to create, write, and erase files in it. 

The TMPDIR environment variable will either be set for the client’s environment or the Data Server’s environment, depending on how the file is accessed. If the file is opened via a drive or directory (not via a Data Server), then the TMPDIR environment variable must be set in the client’s environment. That means that the TMPDIR variable should be set to point to a valid directory before BBx is run. If the file is accessed via the Data Server, then the TMPDIR environment variable must be set in the Data Server’s environment as well. That means that the TMPDIR variable should be set to point to a valid directory in the shell before the Data Server is initially started. If the Data Server is started via a cron script, then the variable should be set in the script just before the server is invoked. 

If the client or Data Server is running under NT, then the TMPDIR variable should be set from the Control Panel – System – Environment. Note that you may have to reboot the NT machine in order for the variable to take effect. 

If the client is running under Windows 95, then the TMPDIR variable should be set from the autoexec.bat file, so that it takes effect for all sessions under Windows. 

If the TMPDIR variable has already been set and the error persists, ensure that: 

1) The directory pointed to by the TMPDIR environment variable exists. 
2) BBx can write to that directory so that it can create the sortby file (the drive is not dsksyn’d). 
3) The drive has enough space. 
4) You have adequate permissions to write to that directory. 
5) If it is a local drive under DOS/Novell, ensure that SHARE is loaded. 

Last Modified: 02/23/2004 Product: BASIS ODBC Driver Operating System: All platformsError Number: 5 OS Error: 21

BASIS structures five components of their technology into the BBx Generations.