SAS Management Console failed to register table - query executed with no schema name

[SASKiwi]

If you can successfully query SAP HANA tables using a SAS LIBNAME or CONNECTION statement, then there's nothing wrong with your ODBC setup and reinstalling won't change anything. The problem appears to be with your configuration of the same SAP HANA library in SMC. As @Sajid01 has asked have you defined a schema in the SMC data library properties?

[dioninoang]

Halo sir,thank you for response.

Yes, i added schema name in the SMC under schema in library properties.

Im successfuly query with

libname test "test/directory";
proc sql;
connect to odbc as mycon
   (datasrc=SAPHANA user=SAS password=Password);
create table test.SAS_PD as
select *
   from connection to mycon
      (select *
      from SAS.SAS_PD 
         );
disconnect from mycon;
quit;

because in there i able to put schema name before the table name.

Using libname or register table via SMC/DI it still throw an error,same error. 

i try to select only 1 column in SAP HANA table to see the log, amazingly the log show that i select all the column.

LIBNAME sash1 ODBC  DATAsrc=SAPHANA SCHEMA=SAS user=SAS Password=Password;

proc sql;
select * from sash1.SAS_LGD;
quit;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         proc sql;
27         select * from sash1.SAS_LGD;
ERROR: CLI prepare error: Unable to retrieve columns for table [SAS_LGD].
SQL statement: SELECT * FROM SAS_LGD.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
28         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.46 seconds
      cpu time 

proc sql;
select '/test/date' from sash1.SAS_LGD;
quit;

26         proc sql;
27         select '/BA1/CR0KEYDAT' from sash1.SAS_LGD;
ERROR: CLI prepare error: Unable to retrieve columns for table [SAS_LGD].
SQL statement: SELECT * FROM SAS_LGD.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
28         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.42 seconds
      cpu time            0.09 seconds
      

[Nigel_Pain]

Can I just check this? The schema name you've specified in your passthrough code ("SAP_SCHEMA") is not the same as the one specified in your libname statement ("SAS"). Is that just your editing for the post? Because if they aren't the same that would explain why one is working and not the other.

[dioninoang]

Halo sir, thank you for response...

Im sory, i edit the code for the post, actually its same name sir

Thank you

[dioninoang]

The only table that i can select using libname is the table that have same level with sys_table in SAP HANA

proc sql;
SELECT CatalogName, SchemaName, TableName FROM sash1.sys_tables WHERE TableType='TABLE';
quit;

Thank you sir

[Sajid01]

Hello @dioninoang 
I have gone through your previous posts and have the following observations.
1.Prima facie you do not appear to have any connectivity issue. Let us leave behind the errors you have come across and focus on the following approach. The initial approach should be to have a process that works. Fine tuning can be deferred for the present.

2.Identify one table and the columns you have access to in that table in the HANA data base.. Select simpler queries and fewer columns for testing.  Avoid using select *. 

3.This step is important. Run the queries (identified in step(2)  in SAS HANA SQL Console or any other GUI tools your company uses.

The user ID should be the same as the one you are using in SAS. Use only the successful queries in the following steps. Do not expect to run a query that has failed in this stage in SAS.

4.Try the successful queries from step (3) above in SAS. Looks like you are comfortable with SAS SQL Pass through, so it is better to limit to this method for the present. Other approaches can be taken up later.

5. If there is any error, compare the outcomes in step (3) with the outcomes in step (4). The SAP HANA resource of your company should be able to help.

6.If you still have issues, then please make sure you post the logs too.

[Sajid01]

Hello @dioninoang 
Any update Sir?

[dioninoang]

Halo sir...

Yes sir, so after struggling with the connection we decided to try alternate driver for HANA. Now we use Hana Client, with this driver i able to use libname to select/insert from/to Hana table

LIBNAME sash1 ODBC  DATAsrc=SAPHANACONN  user=user Password=password;
proc sql;
create table hana_test as
select * from sash1.hana_test
quit;
data hana_test;
set sash1.hana_test;
run;

But im not able to select from sys_table when i try to register a table via SMC, and also im not able to execute delete statement which the previous driver is able to do that 😅...

Thank you sir

[Sajid01]

Well I suggest you approach SAS Tech Support.