Hi everybody,

We're having Fatal Errors running DeNull on specific files. Paradoxically, we don't have the same errors running this program on the file with more fields (one field in BldMstrP, which is not in BldMstr, is NameCat C(1)).
Here is the program:

* DENULLNEW.PRG - stand-alone generic UDF to remove all NULL values from a given table
* By M. Asherman, Copyright (c) 2000, SpaceTime Systems
* 1/13/2000: initial implementation (under VFP 6.0 SP3).
* 1/14/00: adjust logic to avoid blowing up on long ALTER TABLE command lines.
* Modified by Dragan Nedeljkovich to produce a program instead of macro substitution
* This UDF alters the given table structure to disable support of NULL values,
* which implicitly turns NULLs into empty values without complaining.
*
* Uses the reserved alias 'denulltemp' for temporary table opening.

function denullNew && Dragan's revision
* Takes 1 required argument and returns an error code (success = 0).
lparameters table_arg		&& 1 required arg
* table_arg:				path and filename of the table to be altered.
*							This should include the explicit .DBF extension.
*							I considered making this argument optional,
*							with the default being the current alias, but 
*							decided against this because it's too dangerous.

* We may eventually want to move these macros into a separate denull.h include file.
* arbitrary constants for the DENULL utility program
#define DENUL_ALIAS 'denulltemp'	&& reserved alias for this program
*#define DENUL_MAXFLDS 100			&& 109 is max # ALTER TABLE fields before blowing up
						** NSL 4/5/2000 Mike had this set to 109.  Unfortunately it does indeed blow
						** up at 109.   I moved it down to 100 for safety. 
#define DENUL_MAXFLDS 50	&& NSL 4/24/00 Debug problem on server

* macros for DENULL error codes and messages
#define DENUL_ERRN_NOARG 1			&& unique DENULL-specific error code
#define DENUL_ERRM_NOARG "Required table name argument missing."
#define DENUL_ERRN_NOFILE 2			&& unique DENULL-specific error code
#define DENUL_ERRM_NOFILE "Table not found: "
#define DENUL_ERRN_ALINUSE 3		&& unique DENULL-specific error code
#define DENUL_ERRM_ALINUSE "Reserved alias already in use: "

local starttime, nfields, clauses, nclauses, n, elapsed

* note clock reading for generating final timing statistics
starttime = seconds()				&& # seconds since midnight

* do minimal argument validation and simple, obvious error checking
if empty(m.table_arg)				&& missing required table name arg
	wait window NOWAIT "DENULL: " + DENUL_ERRM_NOARG
	return DENUL_ERRN_NOARG			&& fail
endif
if not file(m.table_arg)			&& file not found
	wait window NOWAIT "DENULL: " + DENUL_ERRM_NOFILE + m.table_arg
	return DENUL_ERRN_NOFILE		&& fail
endif
if used(DENUL_ALIAS)				&& reserved alias already in use
	wait window NOWAIT "DENULL: " + DENUL_ERRM_ALINUSE + DENUL_ALIAS
	return DENUL_ERRN_ALINUSE		&& fail
endif

* provide progress indicator
wait window nowait "Removing NULLs from " + m.table_arg + " ..."

* open the given table under a temporary alias
USE (m.table_arg) AGAIN IN 0 ALIAS (DENUL_ALIAS) EXCLUSIVE

* final pre-loop initializations
nfields = afields(fldarray, DENUL_ALIAS)	&& get array of field attributes
clauses = ''						&& list of clauses for the next ALTER TABLE cmd
nclauses = 0						&& # clauses in next command line to be run
* Dragan added generation of a .prg instead of a long macro
cPrg=addbs(sys(2023))+FORCEEXT("alt"+juststem(m.table_arg),'prg') && Generate temp program name
SET TEXTMERGE TO (cPrg)
SET TEXTMERGE on

\Alter table <<m.table_arg>> ;

* loop over all fields, assembling a list of ALTER <fieldname> NOT NULL clauses
for n = 1 to alen(fldarray, 1)		&& loop over array rows - 1 for each field
	if fldarray[m.n, 5]				&& field allows NULL values
		* add another clause to disable NULL support for this field
		clauses = m.clauses + ' ALTER ' + fldarray[m.n, 1] + ' NOT NULL'
		nclauses = m.nclauses + 1	&& keep track of # clauses
		\	Alter <<fldarray[m.n, 1]>> Not Null	;
		
		if m.nclauses >= DENUL_MAXFLDS	&& max # fields per command line reached
			set message to "Running ALTER TABLE up to field # " + ltrim(str(m.n))
			* invoke the ALTER TABLE – SQL Command with &-macro expanded tail
*			ALTER TABLE (m.table_arg) &clauses NOVALIDATE
		\	Novalidate && Nadya Nosonovsky added Novalidate in the command
		IF n<ALEN(fldarray,1)
			\Alter table <<m.table_arg>> ;	
		endif
		
			set message to			&& clear status bar msg.
			clauses = ''			&& clear the list for next command
			nclauses = 0			&& reset counter
		endif
	endif
endfor
\	Novalidate

*!*	 * check for a final ALTER TABLE command
*!*	 if not empty(m.clauses)				&& some unprocessed clauses remain
*!*	 	set message to "Running ALTER TABLE for remaining fields."
*!*	 	* invoke the ALTER TABLE – SQL Command with &-macro expanded tail
*!*	 	ALTER TABLE (m.table_arg) &clauses NOVALIDATE
*!*	 	set message to					&& clear status bar msg.
*!*	 endif
SET TEXTMERGE off
SET TEXTMERGE to
COMPILE (m.cPrg)
IF FILE(FORCEEXT(m.cPrg,"err"))
	=MESSAGEBOX("Generated ' + m.cPrg +' file contains errors")
	MODIFY COMMAND (m.cPrg)
else
	DO (m.cPrg)
endif

* cleanup and return error code
use in (DENUL_ALIAS)				&& close the temporary opening
elapsed = seconds() - m.starttime	&& # seconds elapsed
* generate final NOWAIT confirmation msg, with timing
wait window nowait "Removed NULLs from " + m.table_arg ;
  + " in " + ltrim(transform(m.elapsed, '9999999.99')) + " seconds." 
return 0							&& done - success

I reported this bug to MS couple of months ago, but haven't received any response. This error could be reproduced on the specific file, which is part of database. I can send the file via e-mail, if somebody would be interested to repeat this problem.

We were unable to find the solution except for reducing nClauses to 24. (instead of original 50)

Thanks in advance for your suggestions.