FIX: Repeated SQL SELECT Statements Cause "File is in use" Error (240436)

SYMPTOMS

You run a program, method, or procedure that repeatedly issues SQL SELECT statements in a loop. After a very high number of iterations, the following program error occurs on a SQL SELECT statement:

Program Error

File is in use.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This bug was corrected in Visual Studio 6.0 Service Pack 3.

For more information about Visual Studio service packs, please see the following articles in the Microsoft Knowledge Base:

194022 INFO: Visual Studio 6.0 Service Packs, What, Where, Why

194295 HOWTO: Tell That Visual Studio 6.0 Service Packs Are Installed

MORE INFORMATION

Although the code below uses the SELECT statement to create a cursor, you may also see this error occur with SELECTs that create files or arrays, because intermediate temporary files might be created when joins or unions are used.

Steps to Reproduce Behavior

Run the following code from a program (.PRG) file:

LOCAL lni
SET TALK OFF
CLOSE ALL
CREATE TABLE table1(field1 i)
INDEX ON field1 TAG field1
INSERT INTO table1 (field1) VALUE (1)
USE
FOR lni = 1 TO 1000000
   IF MOD(lni,100) = 0
      @ 2,2 SAY ALLTR(STR(lni))+" Iterations"
   ENDIF
   SELECT field1 FROM table1 ;
      WHERE field1 = 1 INTO CURSOR sqlresult NOFILTER
   USE IN sqlresult
ENDFOR
CLOSE DATA ALL