Problems accessing a SQL server data source

Problems accessing a SQL server data source

This article provides troubleshooting tips for accessing SQL server data sources.

What kind of problem are you having accessing your SQL Server database?

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 an 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:

  1. Go to the Windows Control Panel and click Services(or click Control Panel > Administrative Tools > Services)

  2. Verify whether the service Distributed Transaction Coordinator or DTC is started.

  3. If not, start the Distributed Transaction Coordinator service.

Application fails with message stating "Cannot find stored procedure..." accessing an SQLServer database

This error can occur because the stored procedures for the Java Transaction API (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 ConnectJDBC installation guide.

ERROR CODE: SQL5042 when running a Java application This error can occur when you configure your application to run in the following manner:

  1. you use a type 2 (application) driver running on the gateway to the OS 390

  2. your application is an XA application.
OS 390 does not use XA, but uses SPM. To resolve the problem:

  1. Check your dbm cfg to see that the SPM is not started on the gateway.

  2. Assign a port and set the db2comm variable to TCPIP.

  3. Update the dbm cfg value SPM_NAME to use your machine name.

  4. Start the SPM on the gateway.

Cannot connect to SQL Server 2000 running on Windows Server 2003 when using XA data source

Symptom

When using XA data source to connect to SQL Server 2000, running on Windows 2003, you receive the following exception: 

java.sql.SQLException: [IBM][SQLServer JDBC Driver][SQLServer]xa_open (0) returns -3
You might also see the following exceptions in WebSphere Application Server SystemOut.log:
[9/21/04 16:57:53:284 CST] 558bbb0a FreePool      E J2CA0046E:
Method createManagedConnctionWithMCWrapper caught an exception during creation of the ManagedConnection for resource jdbc/lmDS,
throwing ResourceAllocationException.  Original exception:
com.ibm.ws.exception.WsException: DSRA8100E: Unable to get a XAConnection from the DataSource.
   at 
com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException.<init>

(DataStoreAdapterException.java:244)
   at 
  com.ibm.ws.rsadapter.exceptions.DataStoreAdapterException.<init>
(DataStoreAdapterException.java:171)
   at 
com.ibm.ws.rsadapter.AdapterUtil.createDataStoreAdapterException (AdapterUtil.java:209)
   at 
com.ibm.ws.rsadapter.DSConfigurationHelper.getPooledConnection (DSConfigurationHelper.java:911)
   at 
com.ibm.ws.rsadapter.spi.WSRdbDataSource.getPooledConnection (WSRdbDataSource.java:675)

....
Caused by: java.sql.SQLException: [IBM][SQLServer JDBC Driver]
[SQLServer]xa_open (0) returns -3
   at com.ibm.websphere.jdbc.base.BaseExceptions.createException (Unknown Source)

---- Begin backtrace for nested exception java.sql.SQLException: [IBM][SQLServer JDBC Driver][SQLServer]
xa_open (0) returns -3
   at com.ibm.websphere.jdbc.base.BaseExceptions.createException (Unknown Source)
   at com.ibm.websphere.jdbc.base.BaseExceptions.getException (Unknown Source)

Problem

XA transactions are disabled by default on Windows Server 2003. Microsoft Windows Server 2003, Microsoft Distributed Transaction Coordinator (MS DTC) requires the creation of registry values for all XA DLLs that you plan to use.

Solution Connect JDBC Drivers (DataDirect Connect JDBC, IBM WebSphere embedded Connect JDBC) have the XA DLL sqljdbc.dll that is normally installed on SQLServer_Install_Root\MSSQL\Binn. For example: c:\Program Files\Microsoft SQL Server\MSSQL\Binn) The registry values required for XA transactions are not created automatically. You must create the values manually as follows:

  1. Turn on support for XA transactions:

    1. Open Component Services.

    2. Expand the tree view to locate the computer where you want to turn on support for XA transactions; for example, My Computer.

    3. Right-click the computer name, then click Properties.

    4. Click the MSDTC tab, then click Security Configuration.

    5. Under Security Settings, click the check box for XA Transactions to turn on this support.

    6. Click OK, then click OK again.

  2. Create a registry named-value:

    1. Use Registry Editor and navigate to registry key:
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL

    2. Create a new registry named-value:

      • Name is the file name of the XA DLL (in the format dllname.dll)

      • Type is String (REG_SZ)

      • Value is the full path name (including the file name) of the DLL file

      Name Type Value
      sqljdbc.dll String (REG_SZ) c:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqljdbc.dll

      Note: You must create an entry for each XA DLL file that you plan to use. Also, if you are configuring MS DTC on a cluster, create these registry entries on each node in the cluster.

For more details, see the following Microsoft document:
INFO: Registry Entries Are Required for XA Transaction Support

 

Related tasks


Troubleshooting by component
Example: Accessing data using IBM extended APIs to share connections between container-managed and bean-managed persistence beans

Related reference

Cannot access a data source
Problems accessing an Oracle data source
Problems accessing a DB2 database
Problems accessing a Cloudscape database
Problems accessing a Sybase data source
Extensions to data access APIs

Searchable topic ID: rtrb_dsaccess4