Regarding SAS Stored Procedures

[Nathan4]

I tried calling the stored procedure from Teradata to SAS. But I was getting an error as Invalid session mode. Can anyone help me with this error.

Below is the code and error:

proc sql;
connect to teradata as tera(user = testuser pass= testpass tpid="XXXXX" connection=global);
 Create table work.sasresult as
         Select * from connection to tera(call dbname.spname('2016-01-15','2016-01-30','IND',?,?));
disconnect from tera;
quit;

 proc sql;
connect to teradata (user=&ishid password=&ishpass tdpid="XXXXXX" connection=global);
Execute(
create table work.spresult as
Select *
From Connection To teradata
(

Excute    DB.SP('2017-01-01','2017-01-03','XXX',?,?) by teradata;
)
;
Disconnect From teradata;

quit;

ERROR: Teradata prepare: Invalid Session Mode for procedure execution. SQL statement was Call dbname.spname('2016-01-15','2016-01-30','IND',?,?);

[ChrisHemedinger]

Some hints on the Teradata community here:

https://community.teradata.com/t5/Database/Invalid-session-mode-for-procedure-execution/td-p/57172

You might need to adjust the session mode for how you connect to Teradata, which looks similar to encoding options for SAS (UTF-8 vs ANSI).

[kiranv_]

connect to teradata as tera(user = testuser pass= testpass tpid="XXXXX" connection=global mode = teradata);
 

Try mode = Teradata along with other options in connect statement

[Nathan4]

H Kiran,

I want call stored procedure from teradata to sas eg can you please give me some Exact syntax for that.

Thank you,

Nathan 

[Nathan4]

28 proc sql;
29
30 connect to teradata (user=&ishid password=&ishpass tdpid="xxxxxx" connection=global mode=Teradata );
31
32 Execute (call DB.SP('2017-01-01','2017-01-03',XXX',ORC,OMSG));
_
22
ERROR 22-322: Expecting a name.

Can you Please Help me out that

[kiranv_]

I have never executed stored procedure in Explicit SQL pass through.  but below is example for Oracle stored procedure executed through SAS(found the code on SAS site), I guess it should be similar expect that execute (execute in below code will become execute (call .  I too did not find any solutions online  and If this does not work, you should contact the expert at this kind of things @JBailey who could help you.

proc sql; 

connect to oracle (user=userid password=password path=database_name); 

execute (execute st_pr_name('parm')) by oracle; 

disconnect from oracle; quit;

[ChrisHemedinger]

There are a few other Teradata-specific examples located in this documentation.  As @kiranv_ said, take heed of the proper syntax for the statements you're wanting to run.

  execute (
    call my_stp('param1', 'param2')
  ) by teradata;

[Nathan4]

Hi,

28 proc sql;
29
30 connect to teradata (user=&ishid password=&ishpass tdpid="XXXX" connection=global mode=Teradata );
31
32 Execute (Execute DB.SP( '2016-01-01', '2016-01-03' , 'XXX' ))by Teradata;
ERROR: Teradata execute: 'DB.SP' is not a macro.

[ChrisHemedinger]

What happened to the "CALL" keyword?  Why did you switch to EXECUTE within the Execute block?

I feel like you're thrashing here with your syntax attempts.  Here's an example of a valid SQL step that calls a Teradata stored procedure.

proc sql;
   connect to teradata (user=userid password=xxxx server=myserver mode=Teradata);
   execute 
      (CALL SAS_SYSFNLIB.SAS_SCORE_EP
         (
            'MODELTABLE="grotto”.”sas_publish_model”',
            'MODELNAME=Intr_Tree',
            'INQUERY=SELECT * from ”grotto”.”score_input_table” WHERE x1 < 1.0',
            'OUTTABLE=”grotto”.”sas_score_out1”',
            'OUTKEY=id',
            'OPTIONS=’  /* can be blank or NULL if no options are needed */
         )
      ) by teradata;
   disconnect from teradata;
quit;

[JBailey]

Hi @kiranv_

You are way too kind!

Best wishes,
Jeff

[JBailey]

Hi @Nathan4

I have taken a shot at explaining this via a SAS Communities article.

An Insider's Guide to using Teradata Stored Procedures with SAS/ACCESS

Let me know what you think.

Best wishes,

Jeff