BookmarkSubscribeRSS Feed
Nathan4
Calcite | Level 5

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',?,?);
 
 

 

10 REPLIES 10
ChrisHemedinger
Community Manager

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).

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
kiranv_
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Calcite | Level 5


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_
Rhodochrosite | Level 12

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
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Nathan4
Calcite | Level 5

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
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
JBailey
Barite | Level 11

Hi @kiranv_

 

You are way too kind!

 

Best wishes,
Jeff

JBailey
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1772 views
  • 4 likes
  • 4 in conversation