Skip to Content

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


To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Carlos Amat y Leon

    Hi Erik, thank you for your post. I know it’s been a while since you posted this but I was wondering, Do you know if there’s a way to do this the other way around?? is it possible to connect and query a MSSQL database from HANA studio??

     

    I’ve been trying to find some info on how to accomplish this but have been unsuccessful so far

     

    Thank you

    (0) 
      1. Marlon Ilasaca Zuasnabar

        Hi Eric.

         

        If I want to use Smart Data Access… is necessary to have SQL Server 2012, Can I do with SQL Server 2008 for this way to connect with a convenient ODBC.

         

        The guide says about ODBC for SQL Server 2012 but nothing about 2008.

         

        Thank you for your reply.

         

        Regards

        Marlon.

        (0) 
          1. Marlon Ilasaca Zuasnabar

            Hi Eric,

             

            Thanks !! … now I know I should update our SQL Server 2008 to take advantage the functionality ‘Smart Data Access’.

             

            I tried to do a connection through Linked Server like your tutorial and I succeeded; however, I have one doubt.

             

             

             

             

             

            When I try with:

             

             

              –  SELECT * FROM OPENQUERY(SAP,’select “CardCode”,”CardName” from PRUEBAS_2507.”OINV”‘)

              –  EXEC (‘select “CardCode”,”CardName” from PRUEBAS_2507.”OINV”‘) AT SAP

             

             

             

            Both are OK.

             

             

             

            But when I try with:

             

             

              –  SELECT * FROM SAP..”PRUEBAS_2507″.”OINV”;

             

             

             

            My result is:

             

             

            Msg 7399, Level 16, State 1, Line 2

            The OLE DB provider “MSDASQL” for linked server “SAP” reported an error. The provider did not give any information about the error.

            Msg 7312, Level 16, State 1, Line 2

            Invalid use of schema or catalog for OLE DB provider “MSDASQL” for linked server “SAP”. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

             

             

             

            What am I doing bad?

             

            Thank you a lot.

             

            Regards,

            Marlon.

            (0) 

Leave a Reply