When connecting to MS SQL Server Database via Weblogic Datasource and using XA jdbc driver, the following error is thrown.
<Jun 3, 2014 5:16:49 AM
PDT> <Error> <Console> <BEA-240003> <Console
encountered the following error java.sql.SQLException:
[FMWGEN][SQLServer JDBC Driver][SQLServer]Could not find stored
procedure 'master..xp_jdbc_open2'. at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb9.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.v(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddq.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)at weblogic.jdbc.sqlserver.ddj.m(Unknown Source)at weblogic.jdbc.sqlserverbase.ddel.e(Unknown Source)at weblogic.jdbc.sqlserverbase.ddel.a(Unknown Source)
The cause behind the issue is that the MS SQL Server was not installed with the Stored procedures to enable JTA/XA
Solution
To connect to SQL Server via XA Driver from WLS Datasource you need to install Stored Procedures for JTATo
use JDBC distributed transactions through JTA, your system
administrator should use the following procedure to install Microsoft
SQL Server JDBC XA procedures. This procedure must be repeated for each MS SQL Server installation that will be involved in a distributed transaction.To install stored procedures for JTA:1.
Copy the appropriate sqljdbc.dll and instjdbc.sql files from the
WL_HOME\server\lib directory to the SQL_Server_Root/bin directory of the
MS SQL Server database server, where WL_HOME is the directory in which
WebLogic server is installed, typically
c:\Oracle\Middleware\wlserver_10.x. Note: If you are
installing stored procedures on a database server with multiple
Microsoft SQL Server instances, each running SQL Server instance must be
able to locate the sqljdbc.dll file.Therefore the sqljdbc.dll
file needs to be anywhere on the global PATH or on the
application-specific path. For the application-specific path, place the
sqljdbc.dll file into the :\Program Files\Microsoft SQL
Server\MSSQL$\Binn directory for each instance. 2. From the
database server, use the ISQL utility to run the instjdbc.sql script. As
a precaution, have your system administrator back up the master
database before running instjdbc.sql. At a command prompt, use the
following syntax to run instjdbc.sql: ISQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql where: sa_password is the password of the system administrator. server_name is the name of the server on which SQL Server resides. location is the full path to instjdbc.sql. (You copied this script to the SQL_Server_Root/bin directory in step 1.) The
instjdbc.sql script generates many messages. In general, these messages
can be ignored; however, the system administrator should scan the
output for any messages that may indicate an execution error. The last
message should indicate that instjdbc.sql ran successfully. The script
fails when there is insufficient space available in the master database
to store the JDBC XA procedures or to log changes to existing
procedures.
