PRB: SQL Server Connections Not Closed from Visual Basic

ID: Q77181


The information in this article applies to:


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:


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

Last Reviewed: March 11, 1999