cancel
Showing results for 
Search instead for 
Did you mean: 

Not recommended feature: Session control statement is used in Dynamic SQL

virendra_raval
Product and Topic Expert
Product and Topic Expert
0 Kudos
2,030

From HANA SPS10 onwards, i believe due to security purpose, the following code started giving *warning* as under:

Warning: * 1347: Not recommended feature: Session control statement is used in Dynamic SQL (current dynamic_sql_ses_ctrl_error_level = 1) SQLSTATE: HY000

-------------------------------------------------------------------------------------

CREATE PROCEDURE set_schema(IN sch_suffix VARCHAR(20))

LANGUAGE SQLSCRIPT

AS v_currSchema varchar(50); v_schema varchar(50) ;

v_schema2 varchar(50);

BEGIN

select current_schema into v_currSchema from dummy;

v_currSchema := v_currSchema || '_' || sch_suffix ;;

EXEC 'SET SCHEMA ' || :v_currSchema ;

END

---------------------------------------------------------------------------------------

As we can see the above code tries to dynamically set the session context to the schema name which is dynamically formulated by appending suffix (passed through procedure) to the current user name which is unknown to the module invoking it.

Is there any way i can avoid the warning? I am looking for a workaround. Any feedback or suggestion is appreciated.

Thanks & Regards,

Viren

View Entire Topic
virendra_raval
Product and Topic Expert
Product and Topic Expert
0 Kudos

Ok found out, there are configuration settings available through which it can be turned off.

alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', 'dynamic_sql_ses_ctrl_error_level') = 'silent' with reconfigure

Also, note the above option is NOT available in HANA Studio API, it has to be disabled using command only.

Jorge_Martinez
Explorer
0 Kudos

Hi @virendra_raval, i've tried to execute the sql (alter system....etc) but appears an error message:

Could not execute 'alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', ...' Error: (dberror) [258]: insufficient privilege: Detailed info for this error can be found with guid 'CF3872CAFA31CD43A7C47CCAC7B149B8'.

Do you know any solution for this error?

Jorge_Martinez
Explorer
0 Kudos
The message is: Could not execute 'alter system alter configuration ('indexserver.ini', 'system') set ('sqlscript', ...' Error: (dberror) [258]: insufficient privilege: Detailed info for this error can be found with guid 'CF3872CAFA31CD43A7C47CCAC7B149B8'. Do you know any solution for this error?