12-19-2017 10:37 AM
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;
12-19-2017 10:40 AM
Some hints on the Teradata community here:
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).
12-19-2017 10:56 AM - edited 12-19-2017 11:03 AM
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
12-19-2017 11:27 AM
28 proc sql;
30 connect to teradata (user=&ishid password=&ishpass tdpid="xxxxxx" connection=global mode=Teradata );
32 Execute (call DB.SP('2017-01-01','2017-01-03',XXX',ORC,OMSG));
ERROR 22-322: Expecting a name.
Can you Please Help me out that
12-19-2017 11:40 AM
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;
12-19-2017 11:47 AM
12-19-2017 11:50 AM
28 proc sql;
30 connect to teradata (user=&ishid password=&ishpass tdpid="XXXX" connection=global mode=Teradata );
32 Execute (Execute DB.SP( '2016-01-01', '2016-01-03' , 'XXX' ))by Teradata;
ERROR: Teradata execute: 'DB.SP' is not a macro.
12-19-2017 11:57 AM
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;
01-02-2018 02:41 PM
I have taken a shot at explaining this via a SAS Communities article.
Let me know what you think.