PRB: SQL Server Connections Not Closed from Visual Basic

• 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.

1. 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




2. 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

1. 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.



2. 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.

Keywords          : kbinterop SSrvVisB 
Version           : 4.2
Platform          : OS/2 
Issue type        : 

Last Reviewed: March 11, 1999