Skip to Content
Author's profile photo Former Member

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

/wp-content/uploads/2015/10/13_805196.png

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

/wp-content/uploads/2015/10/1__805159.jpg

/wp-content/uploads/2015/10/2__805160.jpg

      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)

/wp-content/uploads/2015/10/3__805161.jpg

  • Enter the name of your driver (i.e. MSSQL2012 in this case) and navigate tothe extractedzip file and selectsqljdbc.jar

     /wp-content/uploads/2015/10/4__805162.jpg

  • 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)

/wp-content/uploads/2015/10/5__805169.jpg

  • 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

/wp-content/uploads/2015/10/6__805170.jpg

  • 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

     /wp-content/uploads/2015/10/7__805171.jpg

  • 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

/wp-content/uploads/2015/10/8__805172.jpg

  3. Additional Steps

Make sure you create new JCo RFC (for each SDK_* under Connectors). 1 in this case

/wp-content/uploads/2015/10/9__805175.jpg

Any name should Work, For example it can be UDI_<xxx>

/wp-content/uploads/2015/10/10__805181.jpg

Create a corresponding RFC. Make sure the Program ID used is the same as in JCo RFC

/wp-content/uploads/2015/10/11_805182.jpg

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

http://scn.sap.com/community/data-warehousing/bw/blog/2012/11/29/errors-in-rsa1-when-extract-data-over-the-udi-connection

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.