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'."..
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.
Procedure
- 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, Windows
7, and Windows Server 2008 R2:- 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:
- If you haven't installed IBM® Business Process Manager, download
"Microsoft SQL Server JDBC Drive 3.0" driver from the Microsoft Site
using the URL from Resources section and extract it to any folder.
- If BPM is already installed, go to bpm_install_root/jdbcdrivers/SQLServer/xa to
obtain the files you require in the following steps:
- Copy the sqljdbc_xa.dll file
from the JDBC unarchived directory to the Binn directory
(for a default SQL Server install, the location is C:/Program Files/Microsoft
SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/Binn) 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. For example; from the command prompt, run sqlcmd
-i xa_install.sql. 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.
You can ignore errors about unable to drop procedures that don't exist.
- Open the SQL Server Management Studio to locate
the security folder under the master database. 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 a Lombardi user add master
database in User mappings and check SqlJDBCXAUser role).
What to do next
After you configure the XA transactions and before
you start the server, you must configure your TCP/IP connectivity
using the below steps:
- From Start menu, click .
- Expand
- Locate TCP/IP on the right-hand side.
- Double click TCP/IP and enable it under
the Protocol tab.
- Click the IP Addresses tab to enable the
TCP port for each configured IP address.