Additional Blogs by SAP
cancel
Showing results for 
Search instead for 
Did you mean: 
erik_lemen
Explorer

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

  • Microsoft SQL Server
  • Microsoft SQL Server Management Studio
  • SAP HANA Database
  • SAP HANA client

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:

  1. On my Microsoft Windows client machine (where HANA client and MS SQL Server Management Studio, and Microsoft SQL Server are installed) I
    create an empty text file with the extension .udl (instead of .txt).
    http://www.wenda.io/questions/394369/sql-2005-linked-server-to-oracle-queries-extremely-slow.html
  2. Double-clicking that file launches the following Data Link Properties dialog, where I could see the list of installed OLE DB drivers:
    http://i42.tinypic.com/bfizyu.pnghttp://www.wenda.io/questions/394369/sql-2005-linked-server-to-oracle-queries-extremely-slow.html
  3. I chose the ‘SAP HANA MDX Provider’ and filled out the connection details as shown:
    http://i40.tinypic.com/t8x66q.png
  4. I then opened the .udl file in a text editor to find the OLE DB Provider’s connection string:

    Provider=SAPNewDBMDXProvider.1;Data Source=vanpglnxc25b6.pgdev.sap.corp;Password=secret;User ID=SYSTEM;Location="";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

  5. In Microsoft SQL Server Management Studio, I ran the two stored procedures leveraging that provider and provider string, as follows:

    EXEC sp_addlinkedserver

      @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 */

  6. I then ran a simple SQL Query to test the linked server connection:

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:

http://i39.tinypic.com/ilg6jc.png


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.

  1. I created a 64-bit ODBC System DSN on the Microsoft SQL Server using HANA’s 64-bit ODBC driver:
    http://i40.tinypic.com/zlcepj.png
  2. And named it HANA_HAN_69:
    http://i40.tinypic.com/55pfyv.png
  3. In Microsoft SQL Server Management Studio, I ran the following stored procedures:

    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 */

  4. I then ran a simple SQL Query to test the linked server connection:
    http://i40.tinypic.com/k9t6pl.png
  5. I was also able to query modeled content, such as this Calculation view:

http://i42.tinypic.com/2dujity.png

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.

  1. I was not successful in querying an Analytic View:

    Unfortunately Microsoft is sending a ‘select *’ to the Analytic View.  I expect they have a valid reason for this; however this is not a supported action against a HANA Analytic view.



    http://i39.tinypic.com/2cgjxtt.png

    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".

  2. I was also, surprisingly, not successful in querying a table that contained a primary key:
    http://i41.tinypic.com/2eaute1.pngMsg 7356, Level 16, State 1, Line 1

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


7 Comments