Sunday, January 24, 2010

How to make MSSQL Server XA Datasource Work?

I faced a road block while using XA Data source with Microsoft SQL Server. It took a while to crack it down as there were no one article with end to end fix description, hence though of writing this piece.

Issue:

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'."

Resolution:

1. Ensure Microsoft Distributed Transaction Coordinator is installed and running on every SQL Server machine that will participate in distributed transactions.

a. From Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, click Run, type dcomcnfg in the Open box, and then press OK to open Component Services.

b. Expand Component Services, Computers and right-click My Computer, and then select Properties.

c. Click the MSDTC tab, and then click Security Configuration.

d. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

e. Click OK again to close the Properties dialog box, and then close Component Services.

f. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes

2. Install the Microsoft SQL Server JDBC driver 2.0 from the url in the reference section.

3. Copy the sqljdbc_xa.dll from this directory to the Binn directory of every SQL Server machine that will participate in distributed transactions.

4. Execute the database script xa_install.sql on every SQL Server machine that will participate in distributed transactions.

Reference:

http://msdn.microsoft.com/en-us/library/aa342335.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyID=99B21B65-E98F-4A61-B811-19912601FDC9&displaylang=en


9 comments:

Ramakrishna said...

I got the same problem. My OS id windows 7. When I click on the MSDTC tab, I did not see any Security Configuration.

Please help me to solve the problem.

Thank you,
-Ramakrishna

supertime said...

Thank a lot! My problem was solved by following your doc!

Senthil Balakrishnan said...

Thanks for the comment "Super Time"

Marco Rietveld said...

Thanks! Concise, clear and exactly what I was looking for. :)

Sarang said...

Hello,

Like Ramakrishna, i am also facing the same issue. I have windows 7 and m not getting the "security Configurations" on the MSDTC tab.

Request you to please help me in this regard.

Thanks,
Sarang
sarang@pocit.co.za

PK said...

How to fix this issue in Windows 7 ? When I click on the MSDTC tab, I did not see any Security Configuration.

PK said...

How to fix this issue in Windows 7 ? When I click on the MSDTC tab, I did not see any Security Configuration.

Senthil Balakrishnan said...

Hi PK, Sorry, I never tried with windows 7, I did this as part of a project more than a year before.

Piotr Ignaczak said...

In Windows 7 and Windows Server 2008 you need double click on My Computer and then last one of the option beneath is Distributed Transaction Coordinator folder. Open it and open properties of the Local DTC service and you'll get the security tab.