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;
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).
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
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
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
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;
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;
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: