You must configure XA transactions after the Microsoft SQL Server database is
installed and before you start the server. The SQL Server JDBC driver
provides support for Java Platform, Enterprise Edition/JDBC 2.0 optional
distributed transactions. JDBC connections obtained from the SQLServerXADataSource class
can participate in standard distributed transaction processing environments
such as Java Platform, Enterprise Edition (Java EE) application servers.
About this task
Failure to configure the XA transactions can result
in the following error when the server starts:javax.transaction.xa.XAException:
com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create
the XA control connection. Error: "Could not find stored procedure
'master..xp_sqljdbc_xa_init_ex'."..
Procedure
- The MS DTC service should be marked Automatic
in Service Manager to make sure that it is running when the SQL Server
service is started. To enable MS DTC for XA transactions, you must
follow these steps:
On Windows XP and Windows
Server 2003:- Select .
- Select and right-click My
Computer, and select Properties.
- Click the MSDTC tab, and then click Security
Configuration.
- Select the Enable XA Transactions check
box, and then click OK. This will cause a MS
DTC service restart.
- Click OK again to close the Properties window,
and then close Component Services.
- Restart SQL Server to ensure that it syncs up with the MS DTC
changes.
On Windows Vista and Windows 7:- Select .
- Select .
- Right-click Local DTC and then select Properties.
- Click the Security tab on the Local
DTC Properties window.
- Select the Enable XA Transactions check
box, and click OK. This will restart the MS
DTC service.
- Click OK again to close the Properties
window, and then close Component Services.
- Restart SQL Server to ensure that it syncs up with the MS DTC
changes.
- Configure the JDBC Distributed Transaction
Components:
- Download "Microsoft SQL Server JDBC Drive 2.0" driver from the
Microsoft Site using the URL from Resources section.
- Extract the archive to any folder.
- Copy the sqljdbc_xa.dll file from the JDBC
unarchived directory to the Binn directory
of SQL Server computer. If you are using XA transactions with a 32-bit
SQL Server, use the sqljdbc_xa.dll file in the x86 folder,
even if the SQL Server is installed on a x64 processor. If you are
using XA transactions with a 64-bit SQL Server on the x64 processor,
use the sqljdbc_xa.dll file in the x64 folder.
- Run the xa_install.sql database script on
SQL Server . This script installs the extended stored procedures that
are called by sqljdbc_xa.dll. These extended
stored procedures implement distributed transaction and XA support
for the Microsoft SQL Server JDBC Driver. You will need to run this
script as an administrator of the SQL Server instance.
- To grant permissions to a specific user to participate in distributed
transactions with the JDBC driver, add the user to the SqlJDBCXAUser
role in the master database (for example, for lombardi user add master
database in User mappings and check SqlJDBCXAUser role).