Data access problems - Microsoft SQL Server data source
What is the Microsoft SQL Server problem?
- ERROR CODE: 20001 and SQL STATE: HY000 accessing SQLServer database
- Application fails with message stating "Cannot find stored procedure..." accessing a Microsoft SQL Server database
- ERROR CODE: SQL5042 when you run a Java application
- JAVAX.TRANSACTION.XA.XAEXCEPTION occurs with error: XP_SQLJDBC_XA_INIT
ERROR CODE: 20001 and SQL STATE: HY000 accessing SQLServer database
The problem might be that the distributed transaction coordinator service is not started. Look for an error similar to the following example when attempting to access a Microsoft SQL Server database:ERROR CODE: 20001 SQL STATE: HY000 java.sql.SQLException: [Microsoft][SQLServer JDBC Driver] [SQLServer]xa_open (0) returns -3 at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) ... at com.microsoft.jdbcx.sqlserver.SQLServerDataSource.getXAConnection (Unknown Source) ...To confirm this problem:
- Go to...
Windows Control Panel | Services...or...
Control Panel | Administrative Tools | Services- Verify whether the service Distributed Transaction Coordinator or DTC is started.
- If not, start the Distributed Transaction Coordinator service.
Application fails with message stating "Cannot find stored procedure..." accessing a Microsoft SQL Server database
This error can occur because the stored procedures for the JTA feature are not installed on the Microsoft SQL Server.
To resolve the problem, repeat the installation for the stored procedures for the JTA feature, according to the DataDirect Connect for JDBC driver installation guide.
ERROR CODE: SQL5042 when you run a Java application
This error can occur when you configure the application to run in the following manner:
- You use a type 2 (application) driver running on the gateway to the OS 390
- Your application is an XA application.
OS 390 does not use XA, but uses SPM. To resolve the problem:
- Check the dbm cfg to see that the SPM is not started on the gateway.
- Assign a port and set the db2comm variable to TCPIP.
- Update the dbm cfg value SPM_NAME to use the machine name.
- Start the SPM on the gateway.
JAVAX.TRANSACTION.XA.XAEXCEPTION occurs with error: XP_SQLJDBC_XA_INIT
For a Microsoft SQLSERVER database on Windows Server 2003, and you move a cluster of SQL servers from one node to another node, or shut down the cluster, the Enable XA Transactions setting in Windows Component Services might be reset to disabled. When this happens, a JAVAX.TRANSACTION.XA.XAEXCEPTION with Error: XP_SQLJDBC_XA_INIT might occur because the JDBC driver is no longer enabled for XA transactions.
To re-enable XA transactions in Windows Component Services...
- From the Microsoft Windows desktop, click...
Start | Settings | Administrative Tools | Component Services
- Expand the tree view to locate the computer where you want to turn on support for XA transactions; for example, My Computer.
- Display the context menu for the computer name, and then click Properties.
- Click Options, and then set Transaction Timeout to a length of time that suits the environment. The recommended minimum setting is 180 seconds.
- Click MSDTC , and then click Security Configuration.
- Under Security Settings, select XA Transactions to enable this support.
- Click OK to save the changes.
The installation documentation for JDBC XA connectivity refers to two known problems. See the Microsoft support site for more information:
- KB899756: Windows 2003 only. MSDTC does not detect process termination, and MSDTC cannot set time-out values for XA transactions in Windows Server 2003.
- KB318818: Performance slows down when you use XA Transactions with SQL Server.
Related tasks
Example: Using IBM extended APIs to share connections between CMP beans and BMP beans
Related Reference
Data access problems
Data access problems - Oracle data source
Data access problems - DB2 database
Data access problems - Apache Derby database
Data access problems - Sybase data source
Extensions to data access APIs