17.  Microsoft SQL Server

17.1. Known issues with SQL Server

Example 2.13.  Example properties for Microsoft SQL Server

openjpa.ConnectionDriverName: com.microsoft.sqlserver.jdbc.SQLServerDriver
openjpa.ConnectionURL: \
    jdbc:sqlserver://SERVER_NAME:1433;DatabaseName=DB_NAME;selectMethod=cursor;sendStringParametersAsUnicode=false

17.1.  Known issues with SQL Server

  • When using a Microsoft SQL Server JDBC Driver v1.2 or earlier, the ConnectionURL must always contain the selectMethod=cursor string, which is necessary for the driver to properly support large result sets.

  • When using a Microsoft SQL Server JDBC Driver v1.2 or earlier, the JDBC driver has bugs that manifest themselves when prepared statements are pooled. Please disable prepared statement pooling by including the MaxCachedStatements=0 configuration property in your org.apache.openjpa.ConnectionFactoryProperties.

  • SQL Server date fields are accurate only to the nearest 3 milliseconds, possibly resulting in precision loss in stored dates.

  • Adding sendStringParametersAsUnicode=false to the ConnectionURL may significantly increase performance.

  • The Microsoft SQL Server driver only emulates batch updates. The DataDirect JDBC driver has true support for batch updates, and may result in a significant performance gain.

  • Floats and doubles may lose precision when stored.

  • TEXT columns cannot be used in queries.

  • When using a SQL Server instance that has been configured to be case-sensitive in schema names, you need to set the "schemaCase=preserve" parameter in the openjpa.jdbc.DBDictionary property.

  • SQL Server 2005 does not support native sequences. If you would like to use generated values with SQL Server you should use GenerationType.IDENTITY, GenerationType.TABLE, or GenerationType.AUTO.

  • The use of LOB streaming is limited. When reading LOB data from the database, the Microsoft SQL Server driver will actually load all the data into memory at the same time.

  • The SQL Server 2008 DATETIME2 data type supports 7 digits sub-second precision. When DataDirect JDBC driver is used with SQL Server 2008, setTimestamp method call with a java.sql.Timestamp argument of more than 3 digits precision in a prepared statement will result in truncation. This may cause loss of data precision or optimistic lock exception if an entity uses Timestamp type as version field.