Skip to Content
Author's profile photo Jimmy Yang

Smart Data Access – Basic Setup and Known Issues

Best Practices Guide

I’ve attached a guide that outlines general best practices with Smart Data Access. Please have a look as it’s a great resource!

SDA Modeling Recommendations 2016

Purpose

This blog will focus on basic setup of Smart Data Access (SDA) and then outline some problems that customers have encountered.  Some of the issues outlined in the troubleshooting section come directly from incidents that were created.

There is already a lot of information on Smart Data Access which this blog does not aim to replace.  Throughout the blog, I will reference links to other documentation that can cover the topics in more detail.

What is Smart Data Access (SDA)?

SDA allows customers to access data virtually from remote sources such as Hadoop, Oracle, Teradata, SQL Server, and more. Once a remote
connection to a data source is done we can virtually connect to the tables and query against is or use in data models as if it were data that resides in a SAP
HANA database.

This allows it so customers do not have to migrate or copy their data from other databases into a SAP HANA database.

How to Setup SDA?

Smart Data Access was introduced in SAP HANA SP6, so if you intend on using SDA be on at least this revision.

Prior to connecting to a remote database you will need to configure an ODBC connection from the server to the remote database. For
assistance on how to install the Database drivers on how to install the database drivers for SAP HANA Smart Data Access please refer to SAP note
1868702 and refer to the SAP HANA Learning  Academy videos

https://www.youtube.com/watch?v=BomjFbJ25vo&index=16&list=PLkzo92owKnVx_X9Qp-jonm3FCmo41Fkzm

Documentation on SDA can be found in the SAP HANA Admin guide chapter 6

http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf

Changes to SDA?

For information on what upgrades to SDA has occurred with each revision of SAP HANA please feel free to review the following

SP6 -> SP 7 delta

http://www.saphana.com/servlet/JiveServlet/previewBody/4296-102-7-9005/HANA_SPS07_NEW_SDA.pdf

SP7 -> SP 8 delta

http://www.saphana.com/docs/DOC-4681

What Remote Sources Are Supported (As of SP8)?

Hadoop

Teradata

SAP HANA

Oracle 12c

Sybase ASE

Sybase IQ

DB2 (SP8)

Microsoft SQL Server 2012

Apache Spark (SP8)

** Please note that you could connect to other databases via ODBC, but please note that we cannot guarantee that it will work. **

How To Add a Remote Source

Once you have configured your ODBC files to the external data source of your choosing you can setup a connection to that source in
Studio by doing the following (we are using Oracle in our example)

  1. Expand your system -> Provisioning
    /wp-content/uploads/2014/10/addpng_560985.png
  2. Right click on the Remote Sources folder and select New Remote Source…
    /wp-content/uploads/2014/10/addremote_560986.png
  3. The main window pane will request you to enter in the connection information/wp-content/uploads/2014/10/addadapter_560990.gif
  4. Click on the Adapter Name drop down select the appropriate adapter (for this example we will select Oracle).  The main window pane will request
    you to enter in the connection information
      1. ASE – Adaptive Service Enterprise: version 15.7 ESD#4
      2. Teradata – version 13 and 14
      3. IQ – Version 15.4 ESD#3 and 16.0
      4. HANA – HANA revision 60 and up
      5. HADOOP – HDP 1.3 support added SP7
      6. Generic ODBC – This to connect to other databases that support ODBC protocol, however we do not guarantee that it will
        work
      7. Oracle – Oracle 12c support added in SP7
      8. MSSQL – Microsoft SQL Server ver11 support added in SP7
      9. Netezza – Netezza version 7 added in SP8
      10. DB2– DB2 UDB version 10.1 added in SP8
  5. Fill in connection properties and credentialsORACLE.gif
  6. Press the execute button to save this connection
    /wp-content/uploads/2014/10/executed_560992.gif
    ** As an alternative, you can create a remote source through SQL using the following command: CREATE REMOTE SOURCE <Name>
    ADAPTER “odbc” CONFIGURATION FILE ‘property_orcl.ini’ CONFIGURATION ‘DSN=<DSNNAME>’ WITH CREDENTIAL TYPE ‘PASSWORD’ USING ‘user=<USERNAME>;password=<Password>’;
  7. Press the Test Connection button to verify the connection to the source is successful
    /wp-content/uploads/2014/10/test_561005.gif
  8. Under the Remote Source you will now see your connection
    /wp-content/uploads/2014/10/created_561006.gif

How To Access The Virtual Tables

  1. After adding your New Remote Source, expand it  to see the users and the tables

    /wp-content/uploads/2014/10/virtualtable_561011.gif

  2. Right click on the table you would like to access and select ‘Add as Virtual Table’
    /wp-content/uploads/2014/10/addvirt_561012.gif
  3. You will then choose the alias name and the schema you would like to add this virtual table/wp-content/uploads/2014/10/virt2_561013.gif
  4. After hitting create you will get a confirmation message

    /wp-content/uploads/2014/10/success_561014.gif

  5. Now you can check the schema you have chosen
    /wp-content/uploads/2014/10/virtdone_561015.gif
  6. If you select ‘Open Definition’
    /wp-content/uploads/2014/10/def_561016.gif
  7. You will see under the ‘Type’ it says ‘Virtual’

/wp-content/uploads/2014/10/typevirt_561017.gif

Known Issues and Best Practices

There have been a lot of updates to Smart Data Access and a lot of bugs have been fixed in newer revisions.

Here are some key links you should read for that information:

1868209 – SAP HANA Smart Data Access: Central Note

2180119 – FAQ: SAP HANA Smart Data Access

** MORE issues will be updated at the bottom **

Authors

Man-Ted Chan

Jimmy Yang

Assigned Tags

      58 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Jimmy,

      Good Work.

      Thanks,

      Hari

      Author's profile photo Former Member
      Former Member

      Hi,

      How to raised queries & How to get Solutions.

      Pl. suggest me. Where we have to see ans.

      Author's profile photo Holger Schäfer
      Holger Schäfer

      Hi JImmy,

      i just started using MSSQL Remote Sources.

      Virtual Tables are working well and i am also able to preview data.

      Starting with calculation views and trying to join two tables, i will get an error on Data Preview from modelling perspective:

      >Cannot get the data provider outline

      SAP DBTech JDBC: [403]: internal error:  [403] Error opening the cursor for the remote database [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command for query "SELECT "GSLOCKSYS2_zw_pbuchung"."persnr", "GSLOCKSYS2_zw_pbuchung"."zeitpkt", "GSLOCKSYS2_zw_pbuchung"."art" FROM timacnt."dbo"."zw_pbuchung" "GSLOCKSYS2_zw_pbuchung""

      I found some similar issues on SAP Crystal Report.

      As i understood, joins/unions are supported my SDA.

      The issue seems to be that it is not possible to fire multiple selectes inside one connection.

      Is this a known issue? Is there a specific connection pram for unix odbc connection to avoid this?


      Any help is appreciated 😉


      Cheers Holger


      UPDATE!!!


      Currently i have solved the issue by adding


      MARS_Connection=yes;


      to the odbc config.


      Sadly this is not the whole story because under SP82 there are some issues that crash the complete HANA Stack!


      There is a crash dump concerning SQL Bind and OdbcAccess and federation!


      The normal calls via SQL script are working fine, but my calculation views with JOINS crashed our HANA instance.


      I will retry after upgrading to SP85.


      Sadly we can not upgrade to SP9 because we one of the customers who are using SmartFinancial productive and this will be compatible for SP9 at the end of April 2015 ;-(


      Have nice holidays...



      Author's profile photo Former Member
      Former Member

      Hi Jimmy Yang

      Can you share best practices around usage of SDA?

      For ex: Data Volume restrictions, Data transformations limitations etc.

      Thanks

      Swapnil

      Author's profile photo Sandeep Sharma
      Sandeep Sharma

      Hi,

      I am trying to connect to Teradata. Extra Adapater Properties is a mandatory field. I have no idea and did not find any details about this.

      Can you please help and guide.

      Teradata Remote Source.jpg

      Author's profile photo Selvarasan Subramanian
      Selvarasan Subramanian

      Alternatively can you please try running the below script

      create remote source TD adapter tdodbc configuration ‘DSN=TD’ with CREDENTIAL TYPE 'PASSWORD' USING 'user=user;password=password'

      -Selva

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Sandeep,

      Adjust the connection mode to "Data Source Name" and then this remote connection can be created.

      Best Regards,
      Jimmy

      Author's profile photo Sandeep Sharma
      Sandeep Sharma

      Thanks Jimmy and Selva.

      Its giving odbc error now. Working with my Basis team to get drivers checked on server side.

      Author's profile photo Patrick Bachmann
      Patrick Bachmann

      Hi Jimmy,

      Is it possible to consume a modeled view via Virtual Connection?  ie: instead of table create a virtual connection to "_SYS_BIC"."VIEWNAME"?  I'm experimenting with this and getting Error opening the cursor for the remote database.  So I'm tinkering with authorizations however in the back of my mind I'm wondering if it's because it's not possible.

      Thanks,

      -Patrick

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Patrick,

      Let me clarify:
      1) The remote source is also HANA.
      2) There is a view created on the remote source.
      3) On the primary system you create a virtual table from "_SYS_BIC"."VIEWNAME" and then create a view from it.
      4) Data preview gives you a cursor error

      What revision are you using ?

      Best Regards,

      Jimmy

      Author's profile photo Patrick Bachmann
      Patrick Bachmann

      You have it exactly right except step 3 we are not creating a view yet, just trying to do data preview on the virtual table at this point.  And on step 4 it fails with the cursor error.  We are on 82 and 85.

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Patrick,

      You can perform data preview on virtual tables. But is this virtual table can only be created from the tables and views from _SYS_BIC.

      Do you get the cursor error with every single table in the remote source?

      Author's profile photo Patrick Bachmann
      Patrick Bachmann

      Ok I'm going to create a separate post and include you so you get proper credit for helping me out and we can continue the discussion.

      Thanks!

      Author's profile photo Patrick Bachmann
      Patrick Bachmann
      Author's profile photo Lars Breddemann
      Lars Breddemann

      This is a really neat piece of work - thanks for that to the Canadian team!

      The only think I wonder is, why this is not a document instead of a blog post?

      The Difference between a Discussion, Blog Post, Document and Wiki gives good guidance here and personally I think this really would make a great reference document.

      Author's profile photo Former Member
      Former Member

      Hi All,

      while trying to load data Through SDA from SAP hana to Oracle tables, we are facing an issue w.r.t date columns.

      For example:

      Table definition in oracle :

      create table test ( a number,b date);

      created an virtual table vt_test in sap hana.

      CREATE VIRTUAL TABLE vt_test AT ORACLE."NULL".schema_name.TEST;

      table defintion SAP hana :

      create table test ( a integer, b date);

      while executing below query getting following error

      INSERT INTO VT_TEST(A,B) (SELECT a,b FROM test);

      Could not execute 'INSERT INTO VT_TEST(A,B) (SELECT a,b FROM test)' in 259 ms 615 µs .

      SAP DBTech JDBC: [403]: internal error: Error executing query [Oracle][ODBC]Invalid datetime format. for query " INSERT INTO "schema_name"."TEST" ( "A", "B") VALUES (?, ?)";

      we tried out various options like below:

      INSERT INTO VT_TEST(A,B) (SELECT a,to_date(b,'yyyymmdd') FROM test);

      INSERT INTO VT_TEST(A,B) (SELECT 1,'1996-01-02 00:00:00.0' FROM test);

      INSERT INTO VT_TEST(A,B) (SELECT 1,cast (to_char('1996-01-02','YYYY-MM-DD') as date) FROM test);

      Please let me know what could reason for the issue

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi,

      I think there are some differences between the formats between Oracle and HANA.

      If you are inserting DIRECTLY into Oracle, what is the insert statement you use? From there, we may be able to determine the differences between that statement and the one HANA is trying to send to Oracle.

      Best Regards,
      Jimmy

      Author's profile photo Former Member
      Former Member

      Hi Jimmy ,

      Thanks for you reply.

      i'm using below insert statement in oracle to Insert data in to Date column

      insert into oracle_test (a,b) (select a,b from test);

      create table ORACLE_test ( a number,b date);

      create table test ( a number,b date);

      this is working fine at oracle end.

      i have noticed datatype for date column changed to timestamp when created virtual table using below statement.

      CREATE VIRTUAL TABLE vt_test AT ORACLE."NULL".schema_name.ORACLE_test ;

      table defintion in hana:

      create table HANA_test ( a integer, b date); ----- even i tried with b with timestamp datatype as well

      INSERT INTO VT_TEST(A,B) (SELECT a,b FROM HANA_test );

      failing with below error

      Could not execute 'INSERT INTO VT_TEST(A,B) (SELECT a,b FROM test)' in 259 ms 615 µs .

      SAP DBTech JDBC: [403]: internal error: Error executing query [Oracle][ODBC]Invalid datetime format. for query " INSERT INTO "schema_name"."TEST" ( "A", "B") VALUES (?, ?)";

      so tried couple other insert  statements as well but all of the insert statements failing with same error,

      INSERT INTO VT_TEST(A,B) (SELECT a,to_date(b,'yyyymmdd') FROM HANA_test );

      INSERT INTO VT_TEST(A,B) (SELECT 1,'1996-01-02 00:00:00.0' FROM HANA_test );

      INSERT INTO VT_TEST(A,B) (SELECT 1,cast (to_char('1996-01-02','YYYY-MM-DD') as date) FROM HANA_test );

      INSERT INTO vt_TEST(A,B) (SELECT a,cast (To_timestamp(To_Char(b,

                                           'DDMMYYYY HH24MISS'),

                                    'DDMMYYYY HH24MISS') as timestamp) FROM HANA_test );

      INSERT INTO vt_TEST(A,B) (SELECT a,CAST(LPAD(cast(b as NVARCHAR),14,'0') AS date) FROM HANA_test ); 

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Siva,

      Can you create an incident with SAP Support on this? I think the issue could be related to the changing of the datatype to timestamp when the virtual table is created.

      Best Regards,

      Jimmy

      Author's profile photo Michael Healy
      Michael Healy

      Nice Jimmy 🙂 , thanks for this!

      Author's profile photo Former Member
      Former Member

      Hi Jimmy Yang                     Smart Data Access - Basic Setup and Known Issues

      Thanks for the great post.

      I'm linking from one HANA system to another HANA system via SDA. I can see my virtual tables and can even see the data if I query the virtual table.

      I've also managed to create a calculation view from one of the virtual tables and it  saves and activates successfully.

      However, when I then do a display data preview or query the view I get the following error: SAP DBTech JDBC: [258]: Insufficient privilage             .

      If I change my view properties so that "Apply Privilages" is on Blank or on  "Anlytical Privilages" instead of the "SQL Analytical Privilages" I then get a No Values message returned to me when I do the data preview or query.

      Any suggestions as to what I can try?

      Thanks,
      Barend.

      Author's profile photo Michael Healy
      Michael Healy

      You are getting the DBTech JDBC: [258]: Insufficient privileges error because the user you are using does not have the correct privileges.

      Please see this authorisations troubleshooting guide to check which privileges you are missing.

      Author's profile photo Former Member
      Former Member

      Thanks for the prompt reply Michael. I've had a look and cant seem to pin point the privilege(s) that are missing. Will keep you posted.

      Thanks,
      Barend

      Author's profile photo Patrick Bachmann
      Patrick Bachmann

      Barend, I'm guessing that _SYS_REPO does not have access to the schema where your virtual table exists.  So if that schema were named XYZ you would run sql command like this;

      GRANT SELECT ON XYZ TO "_SYS_REPO" WITH GRANT OPTION

      -Patrick

      Author's profile photo Former Member
      Former Member

      Hi Patrick and Michael,

      I managed to get it sorted out by changing the view properties "Default Client" setting from "session client" to "cross client".

      Thanks for everyone's input.

      Regards,
      Barend.

      Author's profile photo Former Member
      Former Member

      Good Day,

      I am new to SAP and Hana, so this may be a easy question.  I am loading hana table via SDA , from Oracle.  When my Oracle View has ' '(space) in a field, the result in the Hana table is ''(no space).  Is this just the way it works?  I have tried looking up this issue though to no avail.  Any documentation you could point me to would be greatly appreciated.  I can prove this out just doing simple counts on the view or table when the field is ='' or =' '.

      Thanks

      Rob

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Rob, Thank you for question. I'll try to help you as best I can. Is the spaces disappearing in the name of the view? or is it the data is missing the spaces? Best Regards, Jimmy SAP HANA Product Support. --------------------------------------------------------------------------------------------------- SAP HANA Troubleshooting WIKI: http://wiki.scn.sap.com/wiki/display/TechTSG/SAP+HANA+and+In-Memory+Computing

      Author's profile photo Former Member
      Former Member

      The space is missing in the Hana table, yet I see the space in the Oracle View.  I think I may be on to something, so here it is.  The SDA defines the column in question as Char (1) whereas I define the Hana table to varchar(60).  I am wondering if that conversion is what is leading me to nothing in the Hana table.  With that, is there any way to change the definition SQL Data Type of a column in the SDA?  Thank you for your time.

      Author's profile photo Former Member
      Former Member

      Good Day,

      Just thought I would give you an update.  By using the cast() functionality in Oracle on the Oracle View I was able to resolve my issue.  This cast() forces the SDA to behave differently. 

      Thank you for your time.

      Rob

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Glad to hear you resolved it Rob

      Author's profile photo Valentino Paderni
      Valentino Paderni

      DBO.jpgHi Jimmy,

      I'm working with SDA on MSSQL and I was able to create remote data source and on hana system as root I'm able to access DSN correctly and run test SELECT cmd and get table.

      Anyway on Hana Studio on the remote data source which give all ok on connection once I expand it I got the DB name I expect and as well che schema dbo, but than if I expand at that point no table at all are listed so that I can't create virtual table.

      Thank you for any tip u can gave me,

      Valentino

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Valentino,

      Thanks for your question.

      I suspect you are encountering a bug that is resolved in revision 111. The bug is related to the GET_REMOTE_SOURCE_OBJECT_TREE procedure.

      To fix this issue, you should upgrade the HANA system to 111 or higher. Also upgrade Studio as well.

      The workaround is to use an older version of HANA Studio. If you use a SPS08 level Studio these tables should appear.

      I hope this helps.

      Best Regards,
      Jimmy

      Author's profile photo Valentino Paderni
      Valentino Paderni

      Hello Jimmy, hana update solved the issue.

      grazie,

      Valentino

      Author's profile photo Gowthaman Jayapal
      Gowthaman Jayapal

      HI,

      I have enabled SDA connection between HANA DB and Sybase IQ. I'm getting the below error. Sometimes connections seems to work fine and sometimes getting below error.

      [14074]{204217}[307/-1] 2016-05-26 11:48:11.083298 e FedTrace         odbcaccess.cpp(03674) : ODBC error: connected: 0 state: 08001 code: -100 [Sybase][ODBC Driver][SQL Anywhere]Database server not found

      Anyone faced this issue.

      Thanks,

      Gowtham

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi,

      I apologize for the delay. Are you able to isql into the IQ system? Please use the exact same DSN as you were using in IQ as well.

      Best Regards,
      Jimmy

      Author's profile photo Gowthaman Jayapal
      Gowthaman Jayapal

      Hi Jimmy - Indexserver of Master blade was restarted which caused connection issues between HANA and IQ in SDA. After we did complete HANA restart the connection works fine now. Thanks for your reply.

      Author's profile photo Santhosh R K
      Santhosh R K

      Hi Jimmy,

      What is the promotion process for SDA table? If SDA is setup in production system, do we need to do import and export method or as an one time activity we need to do manually?

      Author's profile photo Former Member
      Former Member

      Hi Jimmy,

      I am trying to use HANA SDA to connect to MySQL server, but always get error "SAP DBTech JDBC: [476]: invalid remote object name: Unable to retrive remote metadata for .. : line 0 col 0 (at pos 0)";

      I checked my MySQL server and I saw the login success which came from the HANA.

      Connection to MySQL.png

      From SQL statement, remote source look as follows.

      MySQL Table.png

      Have you facing problem same with me?

      Thanks & regards,

      Tino

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Tino,

      From my understanding, MySQL is not a supported remote data source with Smart Data Access. That is why it's giving you that error.

      SDI is essentially next generation SDA so that may be a better option for you in the future.

      SDI Documentation

      Best Regards,
      Jimmy

      Author's profile photo Naresh Gadamsetti
      Naresh Gadamsetti

      Hello,

      When I add a sql server table as virtual table, I get the following error. This is because the column in sql server is defined as varchar(max) and Hana not able to convert.

      Is this limiration in SDA or any one has suggestions to add any options to resolve this.

      Appreciate your suggestions.

      sda sql server error.png

      Author's profile photo Former Member
      Former Member

      Hi,

      I am using HANA Express VM and trying to establish SDA connection to MSSQL Database.

      When I try to establish SDA connection, I get the following error:

      SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: [unixODBC][Driver Manager]Data source name not found, and no default driver specifie

      However, I am able to connect to MS SQL via isql, and I can query the SQL database through the isql commands.

      Any help will be appreciated. Thanks

      Regards,

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Khardu,

      I suspect the issue is the HANA system is using a different odbc.ini file than isql is.

      By default, HANA uses $HOME/.odbc.ini

      On the other hand, isql uses /usr/local/etc/odbc.ini by default.

      What you should do is set the ODBCINI EXPLICITLY to $HOME/.odbc.ini, that way, both HANA and isql will use the same one.

      You can do this by adding an entry into the HANA <sid>adm's profile file (.sapenv.sh or .sapenv.csh).

      For example, .sapenv.sh

      export ODBCINI=$HOME/.odbc.ini

      I hope this helps you.

      Best Regards,

      Jimmy

       

      Author's profile photo Former Member
      Former Member

      Hi Jimmy,

      Thank you for your response. I am using the hanadb user (which is created by default from SAP-delived VM) in suse linux to connect to MS SQL which is on windows 7 box. First I was unable to even open odbc and odbcinst files from hana db user, then I logged in as root user and gave all the read/write/execute permission to these .ini files. Then i was able to connect.

      I have attache three images to show you all my configuration of unixodbc and paths to drivers.

      when I run strace (as hana user), I can see the permission denied error on Microsoft odbc driver file (attached image no. 02), even though I have granted full permission on this file.

      I have also given in here the strace of isql while connecting to MSSQL both with and without using sudo. Please take a look and guide me where should I go from here?




      I am very new to this linux and hana, this is the first time I am using linux. For the past two weeks, I am just wandering around searching for the solution. Hoping to see this solved this time around.

      Thanking you.

      Regards,
      khardu

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      Hi Khardu,

      The first error you had is not descriptive enough to give you something to act on.

      You should add a -v to give you a more verbose error.

      So, firstly, please execute "isql -v MSSQLTest sa SAPB1Admin"

      I'm just curious what that error message will be, but it looks like it is something with permissions.

      The fact that you sudo into root, and then it works, may indicate more of an issue with dependent libraries, not the libmsodbcsql-11.0.so.2260.0 library directly.

      Compare the results of:

      ldd libmsodbcsql-11.0.so.2260.0

      and

      sudo ldd libmsodbcsql-11.0.so.2260.0

      Cheers,

      Jimmy

      Author's profile photo Former Member
      Former Member

      Dear Jimmy,

      Thank you for your followup.

      I ran " isql -v MSSQLTest sa SAPB1Admin " and " ldd libmsodbcsql-11.0.so.2260.0 " and  also " sudo ldd libmsodbcsql-11.0.so.2260.0 ". Result are attached. Permission seems to be the reason. I also tried relocation libmsodbcsql-11.0.so.2260.0 to $HOME and other places, it gives the same error of permission denied.

      If I ran " strace isql -v MSSQLTest sa SAPB1Admin " it gives the following error along the way:

      open("/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0", O_RDONLY|O_CLOEXEC) = -1 EACCES (Permission denied)

      What should I do now? Once again I appreciate your support.

      Regards,
      khardu

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

       

      Hi Khardu,

      Yes it's definitely permissions.

      Even if you grant 777 on the library alone, if the directory doesn't have the permissions, then the behavior would be what you are experiencing.

      Please run the following:

      sudo chmod -R 777 /opt/Microsoft

      That should fix it.

      Cheers,

      Jimmy

      Author's profile photo Former Member
      Former Member

      Jimmy you are a life saver!

      sudo chmod -R 777 /opt/microsoft worked. Successfully established SAD connection to MSSQL. I can now create virtual tables and query the MSSQL from HANA Studio.

      Earlier I was granting 777 on only libmsodbcsql-11.0.so.2260.0 and was failing every time I tried to make a connection.

      I posted my this issue on 5 different blogs, on youtube also, but only you resolved this.

      Thank you very much man. I owe you this!

      Regards,
      khardu
       

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      My pleasure. Have a good day.

      Author's profile photo Vishal Bagherwal
      Vishal Bagherwal

      Thanks for the great blog Jimmy, I have performance question.
      Does SDA help in improving query performance of backend source like teradata? Does HANA have any engine for SDA backend data processing? or its just another access point

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

       

      I'm not an expert in SDA performance, but I believe there are some elements of the remote source that is processed on the HANA end and some still on the remote source. So performance can vary.

      Author's profile photo Former Member
      Former Member

      Hi,

      Thanks for this informative blog.

      I am trying out similar SDA from Hive tables.

      I could able to create remote source and view the tables. But while adding any table as virtual table am getting the following error.

       

      Following SAP note 2396577 I tried adding virtual table via SQL. Still its giving the same error.

      Any thoughts on this ?

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

       

      Seems like a known issue: 2396577 - Cannot Add Hadoop Objects As HANA Virtual Tables

      Author's profile photo Mohammed Baderuddin Ashraf
      Mohammed Baderuddin Ashraf

      Hi,

      I have setup Microsoft SQL 2012 database Remote Data Source in HANA 1.00.122.08 also able to create virtual tables in HANA schema and accessing MS SQL data in SAP BW on HANA without any issues however when any Table or View definition is modified i.e. Field addition or modification is done in Microsoft SQL 2012 Database i am not getting changes in HANA remote data source tables or in Virtual tables.

      How can i get the MS SQL Table / View definition in HANA remote data source table and then in Virtual tables in HANA schema.

      Regards

      Mohammed Ashraf

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

      I don't think the ability to view the remote data sources' table definition exists. You can view the virtual table definition in HANA Studio by right clicking the virtual table and selecting Open Definition.

      I hope this helps.

      Best Regards,
      Jimmy

      Author's profile photo Mohammed Baderuddin Ashraf
      Mohammed Baderuddin Ashraf

      Hi Jimmy ,

      In HANA 1.0 if any table definition is changed customer have to create new virtual table and replace it in all queries however in HANA 2.0 virtual table refresh definition have been released. I found this information on below web link:

      https://answers.sap.com/questions/120943/changing-sda-virtual-table-structure.html

      Regards

      Mohammed Ashraf

       

      Author's profile photo Former Member
      Former Member

      Hi Jimmy

      I am experiencing the following when trying to update our Hana server from 1.00.102.02

      to  1.00.102.4:

      An already installed component SAP Hana Smart Data Access is not compatible with SAP Hana DB version 1.00.102.4.
      Sap Hana SDA requires SAP Hana DB version 1.00.110 and greater.

      I am unable to install any server component, it just keeps going back to the prompt.

      You will probably wonder why I am not going to a higher version of Hana. The problem is that the previous Basis guy installed PRD & QAS with 1.00.102.04  but  left DEV on 1.00.102.02.

      Also it's the same version of SDA on all 3 systems.

      How do I bypass this message and able to carry on with installation.

      Should I uninstall SDA  and reinstall after the update?

      Or is there another method.

      Regards Rodney

      Author's profile photo Jimmy Yang
      Jimmy Yang
      Blog Post Author

       

      Hi Rodney,

      Let me confirm a few thing with you.

      Was the download you downloaded similar to this: IMDB_SDA100_05_1-20011324.SAR

      Also, how are you installing Smart Data Access? Are you simply running install.sh?

      Best Regards,
      Jimmy

       

       

      Author's profile photo Former Member
      Former Member

      Hi Jimmy

      I resolved our problem. Let me explain.

      We have a BI Landscape PRD QAS DEV. What happened is that whoever installed Hana BI & SDA had PRD/QAS on 1.00.102.04 while DEV was on 1.00.102.02.  We needed to refresh DEV from PRD but was unable because the maintenance revision differed. So we attempted to upgrade DEV to 1.00.102.04.

      However SDA errors said it needed HANA 1.00.110 and above - it would not allow us to move beyond the upgrade step of HDBLCM. We eventually managed to upgrade Hana to 1.00.102.04 using the following:

      ./hdbupd --ignore=check_plugin_dependencies --check_only

      ./hdbupd --ignore=check_plugin_dependencies

      ./hdblcm --ignore gave us the same error.

       

      The error we got:

      An already installed component SAP Hana Smart Data Access is not  compatible with SAP Hana DB version 1.00.102.4.

      Sap Hana Smart Data Access requires SAP Hana DB version 1.00.110 and greater.

      SAP Hana SDA installed version 1.00.6.001.0

       

      These are the Components we needed to upgrade.

      Hana DB  Upgrade 1.00.102.02  to 1.00.102.04

      Hana Clnt Upgrade  1.00.102.02  to 1.00.102.04

      Hana Studio Upg    2.1.15  to  2.1.18

       

      Regards Rodney