Weblogic and MS SQL Configurations to run projects using SQL Server Connections

Creating the SQL Server connection from Jdeveloper is easy. However, after the project is developed, it has to be deployed on the Weblogic Server, a JNDI needs to be created to connect to the MS SQL Server. Oracle has provided 2 default drivers for MS SQL Server.


*Oracle's MS SQL Server Driver (Type 4 XA) Versions:7.0, 2000, 2005, 2008
*Oracle's MS SQL Server Driver (Type 4) Versions:7.0, 2000, 2005, 2008

The difference between 2 drivers is that one is Transaction Aware and other one is not. There are other drivers also present but they would need specific jars to be placed in the server lib directory, but default drivers would work without any additional configuration on the Weblogic Server. Hence a JNDI to connect to MS SQL Server can be created using any of these drivers very much the same way as you create for Oracle Database.

However, following MS SQL Server configurations also need to be done to be able to connect to MS SQL Server from Weblogic Server.

Create the JTA Procedures

Follow the steps as given here.

Also if you are using the XA driver, you also need to enable XA Transactions on MS SQL Server. Follow the following steps for that. 

1. Turn on support for XA transactions: 
• Go to Control Panel > Administrative tools > Component Services.
• Expand the tree view to locate the computer where you want to turn on support for XA transactions: for example, Component Services > Computers > My Computer. 
• Right-click the computer name, then click Properties. 
• Click the MSDTC tab, and then click Security Configuration. 
• Under Security Settings, click the check box for XA Transactions to turn on this support. (Enable XA Transactions) 
• Click OK, and then click OK again. 
2. Create a registry named-value: 
• Use Registry Editor and navigate to registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL. 
• 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. for example, C:\Program Files\Microsoft SQLServer\ MSSQL.1\ MSSQL\ Binn\ sqljdbc.dll. 
3. Go to Control Panel ->Administrative tools->Services. 
•  Start "Distributed Transaction Coordinator."
4. Please restart the server to make these changes take effect.

Note:
While making the JNDI for data sources on WLS Console, specify the value of property platformClassName as oracle.toplink.platform.database.SQLServerPlatform.

And that's all. Happy Development!

How to connect to MS SQL Server through Jdeveloper 11.1.1.3

To connect to MS SQL Server you need to follow the following steps.


Pre-requisite: You need to have a sqljdbc4.jar file placed in the jdev/lib/folder. It can be downloaded from this  (1033\sqljdbc_3.0.1301.101_enu.tar.gz) path and extracted to get sqljdbc4.jar. Finally it needs to be at jdev/lib location.


1. Go to New Database Connection and create a Database Connection by providing all the required details. Remember to select SQLServer as Connection Type.


2. In the Library section click Browse and add the Library entry as shown below.

3. Click on Test and it says Success....


However, for the projects using MS SQL Server connection deployed on Weblogic server, some Weblogic and MS SQL Server configurations also need to be done that can be found here.