The information in this article applies to:
- Microsoft SQL Server Programmer's Toolkit, version 4.2
SYMPTOMS
When developing programs with the Visual Basic Library for SQL Server
(VBSQL), there are several problems that can occur when connections to SQL
Server are not closed properly. This article describes the symptoms of
these problems and provides information on how to avoid them.
- The following Visual Basic error is returned when the maximum number of
open files that MS-DOS can access has been exceeded:
Error: Too many files
- The following VBSQL error message is returned when an attempt is
made to log in to a SQL server that has no available user
connections:
Error 10010: Read from SQL Server Failed
CAUSE
- This error can occur when developing programs with VBSQL because
each connection to SQL Server requires an MS-DOS file handle. When
connections are not closed properly, these file handles are not
released.
- This error can occur when developing programs with VBSQL because
connections that are not closed properly will remain on the server
as "sleeping processes." While present, these processes consume
valuable user connections and can prevent additional users from
logging on to the server.
WORKAROUND
Both of the problems mentioned above can result when running
programs in the Visual Basic interpretive mode. The problems can be
quickly resolved by closing Visual Basic itself because when Visual
Basic is closed, it frees up any existing MS-DOS file handles that
were left open. In addition, SQL Server removes any associated
"sleeping processes." While this method will resolve the problems
if they occur, there can be negative consequences. For example, if
the "Too many files" error is received, it may be difficult to save
changes made since the last time the project was saved.
Eliminating these problems can be difficult, if not impossible,
because there are many times during the development and debugging
process when the program may have to be terminated prematurely.
However, it is possible to reduce the frequency at which these
types of problems occur. The following precautions will help to
achieve this goal:
- Always call SqlClose() for every connection opened with SqlOpen()
or SqlOpenConnection() prior to exiting the program. Be aware
that there may be a number of ways in which the program can exit
(for example, by using custom buttons or menu options that run
the End statement, by using the Close option on the program
control menu, and/or by using user-defined error handlers).
- Test the program in interpretive mode only when it is necessary
to take advantage of Visual Basic's debugging tools. For general
usability testing, compile the program into an .EXE file and run
directly from Windows.
- During the debugging process, periodically save the project,
close Visual Basic, and restart.
- Always save the project prior to running the program in
interpretive mode. This will help eliminate lost work due to the
"Too many files" error.
- It may be necessary to increase the MS-DOS file handles by
adjusting the "files=" statement in the CONFIG.SYS file.