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

[dioninoang]

Hi everyone, big thanks to let me in here...

with my respect, i wanna ask some question about how to add schema name into output query generated by SAS Library(odbc)?

Im facing a problem with registering a table on SAS Management Console. The error is : "ERROR: No rows returned by columns query for table tableName, no columns registered.".

The table registration window show the correct list of available table on SAP HANA, but when i chose and next the error above showing.

When i see the odbc log, it show the query not followed by schema, just like this:

select * from tableA

, that it suppose to be like this :

select * from schema.tableA.

I check the server properties and find that i already write the schema name under the Database Server in Data Server tab, and when registering SAS Show all table under that schema...

That's make me very confuse,whats wrong with my setting...

Maybe i miss some setting or there is another way,please let me know...

im using third party odbc driver for SAP HANA, and using DataDirect driver manager.

Thank you very much.

and im sory if my language is difficult to understand

[Sajid01]

Hello

It's possible that database access has not been granted.
Please check if you have access to the table at the database level.
Also see if you can access the library directly by passing metadata server.
In my opinion using SAS Access interface to ODBC and third party drivers should not be an issue.

[dioninoang]

Thank you sir...

The SAP team told me that they already grant the access... But, i will ask again to make sure...

Im sory sir,i didnt get about access the library directly by passing metadata server, did you mean using sql pass trough ?

Thank you for the respond and suggestion sir

[Sajid01]

What i meant was connecting to the database server from your Enterprise guide using for example something like

libname mydblib odbc datasrc=mydatasource user=myusr_name password=my_pwd;

or something like this

proc sql;
connect to odbc as myconnection
   (datasrc=mydatasource user=myusr_name password=my_pwd);
select *
   from connection to mycon
      (SQL statements);
disconnect from mycon;
quit;

If these work you have access.

[dioninoang]

Oh, i already try with proc sql direct connection like that, it works but i must add schema name in select statement.

proc sql;
connect to odbc as myconnection
   (datasrc=mydatasource user=myusr_name password=my_pwd);
select *
   from connection to mycon
      (select * from schema.table);
disconnect from mycon;
quit;

With libname and data step, or regular proc sql it throw same error like i registering table from Management Console.

And the odbc trace log

[SASKiwi]

The error suggests that your database user account doesn't have the correct permissions to read the database tables. Talk to your database administrator to get this fixed. 

[dioninoang]

Halo sir... i already contact my db admin about this situation, but need time to check on database side.

hope this is bd access permission problem, not in SAS part.

Thank you sir

[Sajid01]

Hello @dioninoang 
As your able to access database in proc sql using schema_name.table_name , please try this and let us know if it works

libname mydblib odbc datasrc=mydatasource user=myusr_name password=my_pwd schema=schema_name;

Once it works let us look for the next steps.

[dioninoang]

Halo sir. I try this and it also doesnt work sir

libname mydblib odbc datasrc=mydatasource user=myusr_name password=my_pwd schema=schema_name;

data work.table;
set mydblib.tableA;
run;

proc sql;
select * from mydblib.tableA
quit;

I try with MS Sql Server connection the code above is works.

hope this is database user access permission problem, not in the SAS part 

Thank you sir

Thank you sir

[Sajid01]

Hello @dioninoang 

It is good that you have got the admins involved to look into the access issue.

However what is surprising is that you are able to connect with Proc SQL. Is that possible without access ? I don't think so. What I understand from your statements so far, is that the container schema contains the table and that has to be specified. That's understandable.

Given the above fact, your inability to connect using libname with schema specified is a bit surprising. It will be good if you can post  the log after running just the libname statement.  Experienced members of the community / experts from SAS  can guide us.
If there are some GUI front ends like SQL Developer, Toad  or SQL Server management Studio that can connect to the SAP Hana database, please try. If you are able to connect, then access is not an issue.
Again if you are able to connect using Proc SQL, ODBC driver configuration may not be issue, but it is better to check that. 

[dioninoang]

Halo sir...thank you for your respond

When i try run just the libname statement there is no error, it success 

libname test odbc datasrc=SAPHANA user=SASXXX password=Password SCHEMA=SASXXX;

And the log:

And about using proc sql

proc sql;
connect to odbc as mycon
   (datasrc=SAPHANA user=SASXXX password=Password);
select *
   from connection to mycon
      (select *
      from SAPSCHEMA.SAS_Table 
         );

disconnect from mycon;
quit;

I think the script 'connect to odbc' create the environment of master schema, and 'select * from connection to mycon' is open the environment so i can type the table schema in that and become success getting data.

The log show that the query pass to SAP HANA same as my select script, with schema name added

Im still waiting from the database administrator for a good news 🙂

Its very confusing 

Thank you 

[Sajid01]

Thanks @dioninoang  for the response.

It is good news. The libref has been successfully assigned means there is no error in the libname. statement.
Do confirm from the DBA's the level of access you have. (Do you have select access, what columns you have access to etc.  For example n some RDBMS systems ability to connect is not sufficient, one needs create session privilege. Your DBA is the best resource to help.)

Now about your original question where to specify the schema in SAS management console. This is done in the screen where you specify the server details and connection details. Immediately below the server drop down, there is an option for schema.
If you are starting from data library manager and using right click and selecting new library, then it would be the sixth form/screen.
Lastly if you think nothing is working SAS tech Support is the best option.

PS : I just saw the update to your original question. You should be able to add schema and hopefully your issue will be resolved.

[dioninoang]

Halo sir,thank you for the response.

Our dba has confirm that access already given, both me and dba still searching for the cause of this issue...

For temporary im using sql pass through method to read and insert data to SAP HANA.

I will post here if there is any update.

Thank you very much sir

[Sajid01]

Hello
Were you able to add schema to SMC?

[dioninoang]

Halo sir...

Still under investigation sir... our try including uninstall,reinstall odbc driver, and restart service still not working...

Do you know the location of SAS log where SAS execute libname sir?

Thank you very much