SAP provides a few different ways to connect directly to an SAP HANA database through the HANA client. Off the top of my head I’ve worked with the MDX OLE DB provider, 64-bit and 32-bit ODBC drivers, SQLDBC, as well as a JDBC driver; I expect there are other ways I have yet to play with. The other day I worked with a customer asking how to connect Microsoft SQL Server’s Management Studio to a HANA database as a remote linked server.
The customer OSS Support message
The customer had provided their starting point, which was to run the following two stored procedures to setup the link between MS SQL Server and SAP HANA database server:
EXEC sp_addlinkedserver
@server = N'SAP'
, @srvproduct= N'HANA'
, @provider= N'SAPNewDBMDXProvider'
, @datasrc= N'192.168.0.1:30015'
, @provstr= N'DSN=SAP_HANA;UID=SYSTEM;PWD=secret;'
, @catalog= N'_SYS_BIC'
exec sp_addlinkedsrvlogin
@useself = 'FALSE'
, @rmtsrvname = 'SAP’
, @rmtuser = 'SYSTEM'
, @rmtpassword = 'secret'
Pre-requisites
In my environment, I was using Microsoft SQL Server 2008, and SAP HANA Database 1.00 revision 69.
Here are the mistakes I made along the way, in case others are searching by the symptoms encountered…
Attempt #1:
Since it seems that Microsoft’s linked server approach requires an OLE DB driver (based on the stored procedure arguments the customer
gave me, and quick web-based searches), I started with SAP HANA’s MDX Provider.
Here are the steps I took:
@server ='MY_HANA_SERVER' /* arbitrary unique name for SQL Server to reference HANA */
, @srvproduct = 'HANA' /* arbitrary product name, because it cannot be NULL */
, @provider='SAPNewDBMDXProvider.1' /* HANA's MDX OLE DB provider */
, @provstr= 'Integrated Security="";Persist Security Info=True;
Impersonation Level=Anonymous;Mode=Read;Protection Level=None;
Extended Properties="SFC_INSTANCE_NUM=00;SFC_LANGUAGE=EN;";
Initial Catalog="";MDX Compatibility=0;SubQueries=0'
, @datasrc= 'vanpglnxc25b6.pgdev.sap.corp' /* Data Source */
exec sp_addlinkedsrvlogin
@useself = 'FALSE'
, @rmtsrvname = 'MY_HANA_SERVER' /* needs to match @server provided above */
, @locallogin = NULL
, @rmtuser = 'SYSTEM' /* HANA database user's login credentials */
, @rmtpassword = 'secret' /* would not use SYSTEM user in production */
select 'ABC', 123 FROM MY_HANA_SERVER..SYS.DUMMY
I received the following error:
Msg 7317, Level 16, State 1, Line 1
The OLE DB provider "SAPNewDBMDXProvider.1" for linked server "MY_HANA_SERVER" returned an invalid schema definition.
Here are the results:
Attempt #2:
After some research, I determined that HANA’s MDX provider may not be the best/supported approach, as it’s designed to provide results to multi-dimensional queries. It appears this linked server feature is for flat data and is more for T-SQL type statements,
I abandoned the MDX OLE DB provider approach, and went with Microsoft’s OLE DB driver for ODBC.
EXEC sp_dropserver /* only required if there is already a server named ‘MY_HANA_SERVER’ */
@server = 'MY_HANA_SERVER'
, @droplogins = 'droplogins'
EXEC sp_addlinkedserver
@server ='MY_HANA_SERVER' /* arbitrary unique name for SQL Server to reference the HANA server */
, @srvproduct = 'HANA' /* arbitrary product name, because it cannot be NULL */
, @provider='MSDASQL' /* Microsoft's OLE DB provider for ODBC Data sources */
, @datasrc='HANA_HAN_69' /* ODBC System DSN Name */
EXEC sp_addlinkedsrvlogin
@useself = 'FALSE'
, @rmtsrvname = 'MY_HANA_SERVER' /* needs to match @server provided above */
, @locallogin = NULL
, @rmtuser = 'SYSTEM' /* HANA database user's login credentials */
, @rmtpassword = 'secret' /* would not use SYSTEM user in production */
Errors I ran into after the simple queries worked
Keep in mind that at the time this blog was written, I was working with HANA 1.00 revision 69.
OLE DB provider "MSDASQL" for linked server "MY_HANA_SERVER" returned message "[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;7 feature not supported: cannot execute select * on olap cube: OSS.2013.1104505/AN_TEST_LINKED_SERVER: line 1 col 26 (at pos 25)".
Msg 7306, Level 16, State 2, Line 1
Cannot open the table ""_SYS_BIC"."OSS.2013.1104505/AN_TEST_LINKED_SERVER"" from OLE DB provider "MSDASQL" for linked server "MY_HANA_SERVER".
The OLE DB provider "MSDASQL" for linked server "MY_HANA_SERVER" supplied inconsistent metadata for a column. The column "CUSTOMER_ID" (compile-time ordinal 1) of object ""SYSTEM"."MYCUSTOMER"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.
Missing 'WHERE' clauses, OPENQUERY syntax and Pass-through queries
It's been a long time since I've looked at using HANA as a remote linked MS SQL Server. According to the dates on this blog, I guess it's been just over a year. I received a new support ticket today on this topic. The symptom was that there was a noticeable performance issue comparing the runtimes of a SQL statement using HANA Studio directly to the HANA server compared against running the same SQL Statement through MS SQL Server to that HANA server as a linked remote server.
The underlying root cause ended up being related to the 'WHERE' conditions. They were not being pushed from MS SQL Server to HANA, so HANA was retrieving the whole resultset unfiltered, returning it to SQL Server, which was then applying the WHERE-clause filtering itself.
A quick internet search suggested that this is well-known and recommends using OPENQUERY syntax to direct Microsoft's query optimizer to push the whole query to the remote server.
Here are some examples of other databases encountering the same symptoms:
http://technet.microsoft.com/en-us/library/ms188279.aspx
http://dba.stackexchange.com/questions/6860/performance-implications-of-using-openquery-in-a-view
http://stackoverflow.com/questions/3577102/sql-2005-linked-server-to-oracle-queries-extremely-slow