0

I'm looking for a better way to bring in data from Hana into our Sql Server EDW. Currently we're using OpenQuery but I really want to get to using Sql Server's External Tables functionality because then I can easily push much of the compute requirements down to Hana without needing to create complication query strings.

However, my first attempt proved frustrating as something is apparently deciding to always use my default schema and not the one I presented.

This is the test code I'm using to pull back SAPABAP1.TCURR from one of our Hana instances (certain details obscured for the usual reasons):

If Object_Id('SAP.TCURR') Is Not Null
    Drop External Table SAP.TCURR;
Go
If Exists (Select 1 From sys.external_data_sources Where name = 'SAPHANA_HNQ')
    Drop External Data Source SAPHANA_HNQ;
Go
If Exists (Select 1 From sys.database_credentials Where name = 'Me@Hana')
    Drop Database Scoped Credential Me@Hana;
Go
Create Database Scoped Credential Me@Hana With
     Identity = 'rambler'
    ,Secret = '**SuperSecretSquirrelPassword**';
Go
Create External Data Source SAPHANA_HNQ With
    (
         Location ='ODBC://10.10.10.10:30015'
        ,Credential = Me@HANA
        ,Pushdown = On
        ,Connection_Options = 'Driver={HDBODBC};ServerNode=10.10.10.10:30015'
    );
Go
Create External Table SAP.TCURR
    (
         MANDT NVarChar(3)      Not Null
        ,KURST NVarChar(4)      Not Null
        ,FCURR NVarChar(5)      Not Null
        ,TCURR NVarChar(5)      Not Null
        ,GDATU NVarChar(8)      Not Null
        ,UKURS Numeric(9, 5)    Not Null
        ,FFACT Numeric(9, 0)    Not Null
        ,TFACT Numeric(9, 0)    Not Null
    ) With
    (
         Data_Source = SAPHANA_HNQ
        ,Location = '"SAPABAP1"."TCURR"'
    );

Go
Select   *
  From   SAP.TCURR;

However, when I execute this, I get the following error:

Msg 7320, Level 16, State 110, Line 28
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or view not found;259 invalid table name:  Could not find table/view TCURR in schema RAMBLER: line 1 col 77 (at pos 76) .

What am I doing wrong here that's making the driver want to use my schema rambler and not the one I gave it SAPABAP1? I also tried defining the Location as just SAPABAP1.TCURR, but that made zero difference.

For the record, the following OpenQuery returns data back just fine:

Select * From OpenQuery(SAPHANA_HNQ, 'Select * From "SAPABAP1"."TCURR"');

Sql Server 2019 RTM

Rachel Ambler
  • 1,440
  • 12
  • 23
  • If the HANA DB is on a recent version you’re likely using a **tenant** DB. Connecting with just the IP and the default port for instance 00 gets you a connection to the SystemDB - that’s why your application table is not there. – Lars Br. Dec 21 '19 at 02:11
  • But I'm not. Both the Linked Server connection and the one for the External Table's Data Source connect to the Same IP & Port # – Rachel Ambler Dec 21 '19 at 17:55
  • Alright, looking at the error message from SQL Server (should've done that before), I notice, that it tries to find the table in your schema `RAMBLER` instead of using the provided schema name. In the SQL Server docu, the example for Oracle includes an additional dot `.` in front of the schema name for the `Location` parameter. So maybe try `Location='."SAPABAP1"."TCURR"' to make SQL Server use the schema name. – Lars Br. Feb 26 '20 at 01:52
  • Thanks - I'll try that and let you know tomorrow, @LarsBr. – Rachel Ambler Feb 26 '20 at 02:09

2 Answers2

1

could you try defining the schema&object of the remote side?

) With
(
     Data_Source = SAPHANA_HNQ,
     SCHEMA_NAME = 'SAPABAP1',
     OBJECT_NAME = 'TCURR'  
);
lptr
  • 1
  • 2
  • 6
  • 16
  • If I could, I would. I get the following error when I try: Msg 102, Level 15, State 2, Line 28 Incorrect syntax near 'Schema_Name'. – Rachel Ambler Dec 20 '19 at 17:56
  • :) , it seemed so fitting. You could try creating a new credential WITH IDENTITY = SAPABAP1... and see if it makes any difference. Also, in the OPENQUERY : Select * From OpenQuery(SAPHANA_HNQ ...SAPHANA_HNQ is a linked server isnt'it? Check the DSN of the linked server, and whether there is a DATABASE property defined, If there is, then use that in the datasource connection options: Connection_Options = 'Driver={HDBODBC};ServerNode=10.10.10.10:30015;DATABASE=xyz' – lptr Dec 21 '19 at 09:24
  • There is no database setting in the System DSN for the Linked Server. – Rachel Ambler Dec 21 '19 at 17:56
  • then, most likely, it is the security settings of the linked server that "impose" the HANA schema. Could you check If the linked server impersonates/connects as SAPABAP1 (and not as rambler)? – lptr Dec 22 '19 at 07:49
  • It absolutely does not. I set it up just for this test. – Rachel Ambler Dec 22 '19 at 13:10
1

These worked for me

Location = '.SAPABAP1.TCURR'

Dhirendra Patil
  • 134
  • 1
  • 7