topic Proc fedsql does not find the correct schema in SAS Programming

Proc fedsql does not find the correct schema

klroesner — Sat, 07 Dec 2024 15:24:20 GMT

Hello World,

I want to rewrite a simple proc sql into a proc fedsql but I fail already when accessing the SAP HANA Table. Apparently fedsql can't extract the correct schema from the libname. Instead of UPW_EGUIDE, my personal schema P226526 is used. Does anyone have any ideas?

libname UPWEGUID saphana dsn=PDWDM SCHEMA=UPW_EGUIDE CONNECTION=Global AUTOCOMMIT=NO INSERTBUFF=32767 READBUFF=32767 DBCOMMIT=50000 DIRECT_EXE=DELETE CHAR_AS_NCHAR=YES TABLE_TYPE=COLUMN SUB_CHAR=SPACE authdomain='P-USER' DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1; /* this works well */ proc sql; create table erg as SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ; quit; /* doesnt work */ proc fedsql _DIAG; create table erg as SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ; quit;

29 30 libname UPWEGUID saphana dsn=PDWDM SCHEMA=UPW_EGUIDE CONNECTION=Global AUTOCOMMIT=NO INSERTBUFF=32767 READBUFF=32767 30 ! DBCOMMIT=50000 DIRECT_EXE=DELETE CHAR_AS_NCHAR=YES 31 TABLE_TYPE=COLUMN SUB_CHAR=SPACE authdomain='P-USER' DBSERVER_MAX_BYTES=1 DBCLIENT_MAX_BYTES=1; NOTE: Credential obtained from SAS metadata server. NOTE: Libref UPWEGUID was successfully assigned as follows: Engine: SAPHANA Physical Name: PDWDM 32 33 /* works fine */ 34 proc sql; 35 create table erg as 36 SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ; NOTE: Compressing data set WORK.ERG increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.ERG created, with 3 rows and 3 columns. 37 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 38 39 /* doesnt work */ 40 41 proc fedsql _DIAG; 2 The SAS System 16:07 Saturday, December 7, 2024 42 create table erg as 43 SELECT ID, dim_DESC, TS FROM UPWEGUID.DIM_TIME ; Table "UPWEGUID.DIM_TIME" does not exist or cannot be accessed ERROR: [42S02]Table "UPWEGUID.DIM_TIME" does not exist or cannot be accessed (0x81bfc10c) ERROR: [42S02]ERROR: [SAP AG][LIBODBCHDB SO][HDBODBC] Base table or view not found;259 invalid table name: Could not find table/view DIM_TIME in schema P226526: line 1 col 25 (at pos 24) (0x103) NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements. 44 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE FEDSQL used (Total process time): real time 0.26 seconds cpu time 0.02 seconds

Re: Proc fedsql does not find the correct schema

Patrick — Sun, 08 Dec 2024 01:25:50 GMT

You could try and define the connection directly in Proc FedSQL

PROC FEDSQL <SAS-connection-option | CAS-connection-option> <processing-options>;

But... like you I'd expect the libname to also work for Proc FedSQL. I suggest you raise this with SAS Tech Support. If you do so please update the discussion here as well so we can learn something.

Re: Proc fedsql does not find the correct schema

klroesner — Sun, 08 Dec 2024 08:53:08 GMT

Thanks Patrik, I reported this to the technical support 2 weeks ago, but have not yet received a solution. If I get one then I will share it … Klaus

Re: Proc fedsql does not find the correct schema

Patrick — Mon, 09 Dec 2024 13:59:12 GMT

@klroesner

From experience with SAS TS (but same applies also to many other support orgs): Whenever you get an email from them even if it's only some "we're still looking into it" info always send a reply back. The reason being: If you receive an email then the ticket status goes to something like "waiting for input from customer" which can lead to your case not being worked on. If you reply then the status goes back to something where TS needs to take the next action.

Re: Proc fedsql does not find the correct schema

klroesner — Wed, 11 Dec 2024 19:58:12 GMT

SAS Tech Support was able to reproduce the error and has forwarded it to R&D ... I'm curious ....

Re: Proc fedsql does not find the correct schema

klroesner — Fri, 13 Dec 2024 13:20:22 GMT

Hi all,

It works with a three-part name: <libref>.<schema>.<table>:

libname PKMDWH saphana dsn=PDWDM schema=DM_PKMDM_BASIS &sapopt.;

proc fedsql _method libs=(PKMDWH) ;
create table work.test2 as
select * from PKMDWH.DM_PKMDM_BASIS.DIM_ORGE;
quit;

Fancy! 🙂