ODBC Error: Invalid String or Buffer Length--Microsoft Server 2008 32bit vs 2008 R2 64bit

Question

Attempting to connect from Java 6 console app to Microsoft SQL Server 2008 R2 on an Microsoft Windows Server 2008 R2 64bit system via an ODBC System DSN using SQL Server Native Client 10.0. The following source code:

        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String srcURL = "jdbc:odbc:FOO";
            if (dbc == null)
            {
                dbc = DriverManager.getConnection(srcURL);
                dbc.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            }
            else
            {
                dbc.close();
                dbc = DriverManager.getConnection(srcURL);
                dbc.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            }
        }
        catch (ClassNotFoundException cx)
        {
            System.out.println("class not found");
        }
        catch (SQLException sx)
        {
            System.out.println("SQL Exception: " + sx);
            log.info("SQL Exception: " + sx);
    }

Throws error

java.sql.exception [Microsoft] [ODBC Driver Manager] invalid string buffer length

Maddeningly, the same code, with and ODBC System DSN configured in the exact same way, WORKS with MS Server 2008 32bit (non-R2) and MS SQL Server 2008 R2. The Microsoft ODBC driver dlls between the two systems are different versions, 6.0.xxxx vs 6.1.xxxx, which I suspect is the culprit.

Answer 1

Yeah the ODBC Manager version should be the problem. Below is the problem I ran into and the solution I thought of, hope it might be of help to someone else too.

When trying to run query against System ODBC DSN (MS Access .mdb file) from an app deployed to Jboss 4.x, I get same error: "SQL state [S1090]; error code [0]; [Microsoft][ODBC Driver Manager] Invalid string or buffer length" in Windows Server R2.

I reproduced the same error on 2 different Windows Server R2 machines. On Windows Server Standard (I guess R1) and Windows 7 Professional x64 the problem is not reproducible.

Further more, on the same Windows Server R2 when trying to connect directly (from a standalone app), I don't get this problem. If the application won't connect/detect the data source, you'd get an error saying that there is no such DSN name or it's not found. The same error message is thrown when trying to send an empty query to ODBC Data Source (registered Data Source Name - DSN). So i guess the ODBC gets an empty query which tries to execute against the DS and the result is: Invalid string or buffer length.

Since I can read the .mdb file registered as a ODBC DS with given DSN, and I don't get this error when querying it from the standalone app, I'm going to make a standalone app that will read the .mdb file through ODBC and write its content to a .csv file, which the Jboss apps will read.

If anyone finds a better solution, please let me know.