none
transaction left in open state on SQLServer 2000 and JDBC

    Question

  • I have a problem where my connection always has an open transaction as viewed by activity monitor.

    I am using the latest MS SQL Server 2005 JDBC driver (1.1) and connecting to SQL Server 2000.

    This issue arises only when selectMethod=cursor and autoCommit is set to false. This issue does not exist when I connect to SQL Server 2005.

    Has anyone else seen this issue?
    How can this issue be resolved?

    Thanks

    Peter
    Thursday, September 06, 2007 8:04 AM

Answers

All replies

  • Hello Peter,

     

    Does this orphan transaction also appear when using the latest v1.2 CTP driver. You can download it here:

    http://msdn2.microsoft.com/en-us/data/aa937724.aspx

     

    Regards,
    Jaaved

     

    Friday, September 07, 2007 5:08 AM
    Moderator
  • This issue still appears with v1.2 CTP driver.

    I did some more investigating and found that I don't see debug messages like the following when i connect to SQLServer 2000:

    11/09/2007 11:17:52 com.microsoft.sqlserver.jdbc.IOBuffer processEnvChange
    FINE: Connection 1: Transaction 0x0600000033000000 started

    11/09/2007 11:17:52 com.microsoft.sqlserver.jdbc.IOBuffer processEnvChange
    FINE: Connection 1: Transaction 0x0600000033000000 committed

    I also tried the jTDS driver (v1.2.2) and got the following debug messages that might be of help:

    SQLState(S1000) vendor code(16938)
    java.sql.SQLException: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:943)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.cursorCreate(MSCursorResultSet.java:541)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.<init>(MSCursorResultSet.java:154)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:424)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1301)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:414)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
        at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        .....
    SQLState(S1000) vendor code(16945)
    java.sql.SQLException: The cursor was not declared.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:636)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:943)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.cursorCreate(MSCursorResultSet.java:541)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.<init>(MSCursorResultSet.java:154)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:424)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1301)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:414)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
        at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        ......
    java.sql.SQLException: The executeQuery method must return a result set.
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:962)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.cursorCreate(MSCursorResultSet.java:541)
        at net.sourceforge.jtds.jdbc.MSCursorResultSet.<init>(MSCursorResultSet.java:154)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:424)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1301)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:414)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
        at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        ......


    SQLWarning: reason(ResultSet type/concurrency downgraded: [S1000] sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.) SQLState(01000)


    Regards

    Peter
    Tuesday, September 11, 2007 1:25 AM