Step-by-Step guide for Configuration of MSSQL JDBC for BI Extraction
Premise: You need to setup BW data extraction from a remote MS SQL Server (2005 / 2012) on UNIX (AIX / HP-UX based) Systems
Observation:
- Follow this documentation for NW 7.0 based Systems
- AIX / HP-UX based systems do not support DB
Refer SAP Note:1644499 – Database connectivity from Linux to SQL Server
Solution: UDConnect(Uses J2EE Engine)
Refer SAP Note:1601608 – How to access an external Microsoft SQL Server database
Connectivity Support Matrix
Procedure:
1. Download JDBC Driver for MS SQL Server
Refer SAP Note:639702 – JDBC driver for Microsoft SQL Server
Download the latest version of the “Microsoft SQL Server JDBC Driver” from the Microsoft download page; for example:
“Microsoft SQL Server 2005 JDBC Driver 1.2” for SAP releases based on SAP NetWeaver releases between 7.0 and 7.11: This download is no longer provided by Microsoft.
If required, you can download the driver from SAP Service Marketplace:http://service.sap.com/swdc> Database and Database Patches (from other vendors)> MS SQL Server > Database Patches > MICROSOFT JDBC > MS SQL SERVER 2005 JDBC 1.2 > MS SQL SERVER > Microsoft SQL Server 2005 Driver for JDBC version 1.2
Download the zip file and extract it
2. Configure the JDBC
Go to Visual Administrator
- In the Cluster tab, select Server -> Services -> JDBC Connector
- Click on Driver, and click on Create new driver or Datasource button (in case, it is not already present)
- Enter the name of your driver (i.e. MSSQL2012 in this case) and navigate tothe extractedzip file and selectsqljdbc.jar
- Click No when prompt “Choose other files”.
- In the Cluster tab, select Server -> Services ->Connector Container.
- In the right pane, On the Runtime tab, connectors -> connector1.0 -> sap.com/com.sap.ip.sdk.dac.connector.jdbc -> SDK_JDBC
- Select Resource Adapter tab
- Press Add and enter the name of the JDBC driver to add to it to the loader references. This takes in the following format : library:<drivername> (i.e. library:MSSSQL2012)
- Now, Restart the BI JDBC Connector. For that On the Cluster tab -> Services -> Deploy
- In the right pane, On the Runtime tab, Select the BI JDBC connector – sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc
- Press Stop Application and then Press Start Application
- Now BI JDBC Connector is ready for use
For Testing the Connection, follow this steps:
- Select Services -> Connector Container -> Select sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc -> SDK_JDBC
- Click on Managed Connection Factory tab -> Properties tab
- Enter your connection properties, as per below:
Refer: http://wiki.scn.sap.com/wiki/display/EP/JDBC+Connection+Setup
- Save the settings
- Log out of the Visual Administrator.
Now Run the following link to test the connection : http://<hostname>:<port>/TestJDBC_Web/TestJDBCPage.jsp
Example: http://localhost:50000/TestJDBC_Web/TestJDBCPage.jsp
If your connection is successfully configured, a list of tables appears
3. Additional Steps
Make sure you create new JCo RFC (for each SDK_* under Connectors). 1 in this case
Any name should Work, For example it can be UDI_<xxx>
Create a corresponding RFC. Make sure the Program ID used is the same as in JCo RFC
This concludes the Technical setup of UDConnect.
Reference:
SAP Notes as Mentioned Above
http://wiki.scn.sap.com/wiki/display/VC/Step-by-Step+guide+MySQL+JDBC