16.  Microsoft SQL Server

16.1. Known issues with SQL Server

Example 2.13.  Example properties for Microsoft SQLServer

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

16.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.