SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


Connect SAP HANA Database from SQL Server using Linked Server

SQL Server database developers can connect to SAP HANA database using Linked Server configuration and query HANA database tables using SQL query. In this SAP HANA guide and SQL Server tutorial, I will show how SQL Server database administrators and SQL programmers can connect SAP HANA database and execute SQL queries.

The SQL tutorial is formed of following 4 sections:
Installing SAP HANA Database Client
Configuring ODBC for SAP HANA Database Connection using HDBODBC Driver
Creating Linked Server on SQL Server using SAP HANA ODBC Configuration
Execute SQL Query Samples on SQL Server connecting to HANA Database

Install SAP HANA Database Client

First of all we require ODBC drivers to be installed on the server machine that will connect SQL Server programmers from SQL database to SAP HANA database.
We require to download SAP HANA Database Client from SAP and install.

Launch Command Prompt with administrative rights.
Go to file folder where SAPCAR_1110-80000938.EXE and IMDB_CLIENT20_003_119-80002083.SAR exist
Then execute following command

SAPCAR_1110-80000938.EXE -xvf IMDB_CLIENT20_003_119-80002083.SAR

A new file folder with name "SAP_HANA_CLIENT" will be created on current folder and 76 files will be extracted.

extract SAP HANA DATABASE CLIENT using SAPCAR executable

In SAP_HANA_CLIENT folder where compressed setup filed are extracted, there is hdbsetup.exe (SAP HANA Lifecycle Management) file.
Execute it with administrative permissions to install a new SAP HANA Database Client

SAP HANA Database Client installation

Choose setup folder or keep it default. Navigate to following screen on installation wizard.

SAP HANA Lifecycle Management as setup wizard

Review the SAP HANA Database Client Installation task and click Install to proceed setup

SAP HANA Database Client setup progress

When installation finishes successfully close setup wizard.


ODBC Configuration for SAP HANA Database Connection using HDBODBC

Now developers or administrators can define an ODBC connection to target SAP HANA database. First of all launch Data Sources (ODBC) from Administrative Tools or by typing it on Windows menu.
Though the screenshots are from a computer which is installed in Turkish, I tried to explain the steps in English.

Data Sources (ODBC)

When the ODBC Data Sources Administrator tool is displayed switch to Drives tab
You will be able to see HDBODBC driver from SAP SE in the list.
HDBODBC driver is installed on the server machine with SAP HANA Database Client installation

ODBC Data Sources Administrator tool Drivers tab

If HDBODBC is listed as one of the available ODBC drivers, switch to System DSN

System DSN tab on ODBC Data Sources Administrator

Press Add to define a new ODBC system data source. All users performing on the server or computer can benefir from System DSN data sources.

Select HDBODBC as data source driver for ODBC connection to SAP HANA database

HDBODBC data source driver for ODBC connection to SAP HANA database

Now, on this screen administrator has to define a data source alias name for the target HANA database and provide a description for system users.

ODBC Configuration for SAP HANA

For the HANA databaes server and port, you have to get this detail from HANA database administrator.
Or if you are using SAP HANA Studio (Eclipse), you can connect to target SAP system in SAP HANA Development perspective.

SAP server and port for ODBC configuration

You can test your configuration and provide SAP HANA database username and password during this test

test ODBC connection to SAP HANA with user and password

It is necessary to get a successful outcome from the connection test using HDBODBC connection to SAP HANA database

test HDBODBC connection to SAP HANA database


Create Linked Server from SQL Server to SAP HANA Database

Using SQL Server Management Studio, database developers can connect to target SQL Server instance from where they want to connect to SAP HANA database via ODBC connection we have just created in above step.

Under SQL Server instance, drill-through nodes "Server Objects > Linked Servers"
Right click on "Linked Servers" nodes and display context menu

create new linked server on SQL Server using SSMS

On "New Linked Server" dialog screen provide below information including OBDC data source name we have defined before.

connect to SAP HANA database from SQL Server using Linked Server configuration

Same Linked Server can be created via SQL code by executing below sp_addlinkedserver command instead of configurations on SQL Server Management Studio (SSMS)

EXEC master.dbo.sp_addlinkedserver
 @server = N'KODYAZSQL2HANADB',
 @srvproduct = N'HANA',
 @provider = N'MSDASQL',
 @datasrc = N'KodyazHANADb'

create linked server command on SQL Server to SAP HANA database

After Linked Server configuration, SQL programmers can connect to SAP HANA database from their SQL Server development codes using Linked Server easily.


Query SAP HANA Database from SQL Server

Completing all above steps successfully, a database developer on SQL Server can query SAP HANA database tables over Linked Server using HDBODBC configuration.
In this section, I will just provide a few SQL queries using linked server

If you have executed sample HANA database SQLScript, you will have Product table in your SAP HANA schema. It is possible to query SAP HANA database Product table from SQL Server

select top 10 ProductID, [Name]
from [KODYAZSQL2HANADB].."A00087463"."PRODUCT"

SQL query fro SQL Server to SAP HANA Database table

SQL database developers can JOIN a remote table from SAP HANA database with a table in a local database like in following SQL query

select top 10 ProductID, [Name]
from [KODYAZSQL2HANADB].."A00087463"."PRODUCT"

Even SAP HANA database tables can be joined with tables from SQL Server databases as seen in following SQL query.

SQL JOIN from SAP HANA database and SQL Server database tables

It is also possible to query data using public synonyms on SAP HANA database like following queries

select * from [KODYAZSQL2HANADB].."SYS"."M_DATABASES";
select TOP 10 * from [KODYAZSQL2HANADB].."PUBLIC"."M_CS_TABLES"

I hope this tutorial will be useful for both SQL Server developers trying to connect to SAP HANA databases and for HANA database programmers searching for connection between SQL Server and HANA databases.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems