topic Re: SAS Management Console failed to register table - query executed with no schema name in Administration and Deployment

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

dioninoang — Wed, 09 Mar 2022 16:27:53 GMT

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

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

Sajid01 — Tue, 08 Mar 2022 17:10:12 GMT

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.

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

dioninoang — Tue, 08 Mar 2022 17:43:23 GMT

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

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

Sajid01 — Tue, 08 Mar 2022 18:37:16 GMT

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.

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

dioninoang — Tue, 08 Mar 2022 18:53:32 GMT

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

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

SASKiwi — Tue, 08 Mar 2022 21:52:45 GMT

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.

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

Sajid01 — Tue, 08 Mar 2022 22:19:09 GMT

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.

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

dioninoang — Wed, 09 Mar 2022 05:06:24 GMT

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

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

dioninoang — Wed, 09 Mar 2022 05:09:52 GMT

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

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

Sajid01 — Wed, 09 Mar 2022 13:27:10 GMT

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.

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

dioninoang — Wed, 09 Mar 2022 16:23:23 GMT

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

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

Sajid01 — Wed, 09 Mar 2022 17:06:06 GMT

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.

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

dioninoang — Thu, 10 Mar 2022 20:45:57 GMT

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

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

Sajid01 — Fri, 11 Mar 2022 14:49:04 GMT

Hello
Were you able to add schema to SMC?

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

dioninoang — Sat, 12 Mar 2022 16:14:35 GMT

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

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

SASKiwi — Sat, 12 Mar 2022 22:57:07 GMT

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?

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

dioninoang — Mon, 14 Mar 2022 16:02:00 GMT

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

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

dioninoang — Mon, 14 Mar 2022 15:26:17 GMT

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

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

Nigel_Pain — Mon, 14 Mar 2022 15:53:29 GMT

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.

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

dioninoang — Mon, 14 Mar 2022 15:58:49 GMT

Halo sir, thank you for response...

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

Thank you