SYMPTOMS
Consider the following scenario:
- You debug a mixed-language project by using SQL
debugging in Microsoft Visual Studio .NET 2003.
- In the Visual Studio .NET debugger, you use Microsoft ADO.NET to frequently try to connect to a
Microsoft SQL Server database.
In this scenario,
you may receive the following error message:
Connection Timeout
CAUSE
Connection pooling is a feature that is used to improve application
performance. When the application closes a data connection, the connection is
held in a pool. Therefore, the connection can be reused if the application tries to
reopen the connection later.
When you try to use SQL debugging to debug an
application, SQL debugging requires a second unique connection to the database.
However, if connection pooling is enabled, the second connection cannot be
established. The initial connection is reused instead.
However,
when you try to use SQL debugging to directly debug SQL
stored procedures in Visual Studio .NET, this issue is not caused by connection pooling. This issue may occur because one of the following conditions is
true:
- The number of available connections to the SQL Server database is exhausted.
- SQL Server is under a heavy load.
- SQL Server went offline after the SQL stored procedures
were enumerated in Server Explorer.
WORKAROUND
To work around this issue, use one of the following
methods.
Method 1
If you experience this issue when you try to use SQL debugging to debug Transact-SQL statements in an application in Visual Studio .NET, you
must disable connection pooling. To do this, use one of the following methods depending on
the data provider that was used in the application.
Method A: Connection pooling for the Microsoft .NET Framework Data Provider for SQL Server
To control connection pooling, set the
Pooling keyword in the connection string that is used to connect to the
computer that is running SQL Server. For example, use the following connection
string to disable connection pooling for the computer that is running SQL
Server.
Integrated Security=SSPI;server= <MySQLServer> ;Initial Catalog= <MyDatabase> ; Pooling=False;
Note In this sample connection string,
<MySQLServer> and
<MyDatabase> are placeholders for the corresponding values for your system.
Method B: Connection pooling for the Microsoft .NET Framework Data Provider for OLE DB
The .NET Framework Data Provider for OLE DB handles
connection pooling by using OLE DB session pooling. By default, connection
pooling is enabled. To disable connection pooling, use the
following connection string keyword.
OLE DB Services=-4
For example, use the following connection
string.
Provider=SQLOLEDB; OLE DB Services=-4; Initial Catalog= <MyDatabase>; Data Source= <MySQLServer>; Integrated Security=SSPI;
Method 2
If you experience this issue when you try to use SQL debugging to directly debug SQL
stored procedures in Visual Studio .NET, use the following methods that correspond to the conditions that are listed in the "Cause"
section:
- Wait until there are fewer connections to the
SQL Server database.
- Try again when SQL Server is under a lighter load.
- Reestablish a connection to the SQL Server database, and then update the
connection in Server Explorer.