PRB: Unexpected Connections to SQL Server with JDBC

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q313220

SYMPTOMS

When you use multiple JDBC statements on a single connection, you may see multiple connections implicitly created by the Microsoft SQL Server 2000 Driver for JDBC.

Back to the top

If you open your database connection with the SelectMethod property set to Direct (the default setting), only one statement at a time can be active on the connection. When you create a second statement, the driver implicitly opens a new connection to handle the new result set.

Back to the top

RESOLUTION

The following two methods prevent implicit connections:

Close all open statements that are no longer needed.
Set the SelectMethod property to Cursor in your connection string.

Back to the top

STATUS

This behavior is by design.

Back to the top

MORE INFORMATION

Steps to Reproduce the Behavior

The following code demonstrates a scenario where the driver creates an implicit connection:

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver" );
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://myServer:1433;databasename=pubs;SelectMethod=direct","myUser","myPassword");
			
Statement stmt1 = conn.createStatement();
stmt1.execute("INSERT INTO myTable1 (col1) VALUES (1)");
//stmt1.close();  //Uncomment this line to prevent the implicit connection.
			
Statement stmt2 = conn.createStatement();
stmt2.execute("INSERT INTO myTable2 (col1) VALUES (2)");
//stmt2.close(); //Good practice to clean up after each statement.
			
conn.close();

An INSERT statement is used in the example, but the contents of the SQL statement that is used is not important. If you use a tool such as SQL Profiler to observe the connections that are made to the SQL Server, you notice that as soon as the second statement is created, the implicit connection is made.

Back to the top

REFERENCES

For complete details on the SelectMethod property, see the documentation for the Microsoft SQL Server 2000 Driver for JDBC.

Back to the top

APPLIES TO

Microsoft SQL Server 2000 Driver for JDBC

Back to the top

kbjdbc kbprb kbsystemdata KB313220

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top