Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
gorbenkoteh
Active Participant

Prerequisites.



You have SAP HANA database running on Linux OS (for example, on RHEL 7.x)

and remote MS SQL Database running on Windows Server OS as a source system. For testing purposes, you want to know more about data extraction from MS SQL Server into SAP HANA through SDA.



Step 1. Installation of the unixODBC Driver Manager (Linux machine)



From root-user:


Step 1.1. Download unixODBC Driver


Download unixODBC Driver  Manager from official site http://www.unixodbc.org/

On Nov.2020 current version - 2.3.9 , download unixODBC-2.3.9.tar.gz


Step 1.2. Upload unixODBC driver to HANA server


Upload for example in /distr folder


Step 1.3. Install unixODBC Driver Manager



cd /distr

gunzip unixODBC*.tar.gz

tar xvf unixODBC*.tar

cd unixODBC-2.3.9

./configure

make

 make install



Links:

Unix ODBC DriverManager official site


unixODBC





Step 2. Install the Microsoft ODBC driver for SQL Server on SAP HANA Server (Linux machine)



Step 2.1. Download driver from the official site


For RHEL 7.X and Microsoft ODBC Driver 17 driver download link will be - https://packages.microsoft.com/rhel/7/prod/msodbcsql17-17.5.2.1-1.x86_64.rpm

Another ODBC Deiver versions (13,11, etc) - https://packages.microsoft.com/rhel/7/prod/


Step 2.2. Upload and install Microsoft ODBC Driver 13 driver


Upload .rpm file for example to /distr directory on SAP HANA server


From root-user:


Install ODBC driver



rpm -i msodbcsql17-17.5.2.1-1.x86_64.rpm


From <sid>adm-user.


Add the path to ENV variable $LD_LIBRARY_PATH:



export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64


Add path to ENV variable in .bashrc



nano ~/.bashrc


Add


export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64


Step 3. Configure the Microsoft ODBC Driver



Step 3.1. Create MS SQL user


Open MS SQL Management Studio

Connect to Target DB

Security – Users – Create new user

Provide correct permissions for data reading and correct permissions to DB Schema (e.g. dbo or sap or something else)


Step 3.2. Edit odbc.ini file


For example for a database with the name VF3 we can create DSN (Data Source Name) VF3

Typically MS SQL Server uses port 1433 also we need user/password from Step 3.1





nano /etc/odbc.ini


Insert:


[VF3]

Driver=path to the driver from Step 2.1

Description=VF3 Database

Server=ip\instance

Port=1433

Database=VF3

User=<user>

Password=<password>



Step 3.3 Check the connection


From <sid>adm user:



iusql -v VF3 <user> <password>


where VF2 - DSN from Step 3.2

and <user>,<password> - user and password from Step 3.1


Result:





Step 4. Connect SAP HANA to MS SQL Server



Step 4.1.  Open Eclipse / HANA Studio


Provisioning -> Remote Sources - New Remote Source...





Source Name - DSN from Step 3.2

Adapter Name - MSSQL (GENERIC ODBC)

Data Source Name - DSN from Step 3.2

User Name, Password - user and password from Step 3.1


Step 4.2.  Create a Virtual Table


From SAP HANA 2.0 SPS1 this step is optional. We can directly access remote tables and views without having to manually define virtual tables.


Step 4.3.   DML queries on remote data sources 


We can use the following SQL statements:



  • CREATE/ALTER/DROP/REFRESH STATISTICS Statement


  • CREATE SYNONYM Statement


  • DELETE Statement


  • INSERT Statement


  • SELECT Statement


  • REPLACE | UPSERT Statement


  • TRUNCATE Statement


  • UPDATE Statement



Example:

Select Remote Source

In a right-click menu "Open SQL Console"

SQL statement:



SELECT TOP 100 FROM VF3.SAP.LoadXML


Syntax:


<remote_source>.<remote_schema>.<remote_table>



Conclusion.


SAP BASIS Routine task with examples from real life was provided



Links.


Linked Database Overview

Labels in this area