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.
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
Choose setup folder or keep it default. Navigate to following screen on installation wizard.
Review the SAP HANA Database Client Installation task and click Install to proceed setup
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.
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
If HDBODBC is listed as one of the available ODBC drivers, switch to System DSN
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
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.
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.
You can test your configuration and provide SAP HANA database username and password during this test
It is necessary to get a successful outcome from the connection test using 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
On "New Linked Server" dialog screen provide below information including OBDC data source name we have defined before.
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'
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 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.
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.
