Unable to Call Stored Procedure from Teradata to SAS

[siruvuri]

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;

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

Any suggestions or guidance would be greatly appreciated.

Thank you.

[DaveBirch]

To use a Teradata Stored Procedure (or any DBMS Stored Procedure) you need to do it as a CALL EXECUTE, and the results would be a Teradata temporary table (or Global Temporary table, or some kind of scratch table).  You would then use Create table work.sasresult as  Select * from connection  ... to read that table into SAS.

[BonnieS100]

proc sql;

   connect to TERADATA

   (

       SERVER=ABCDE AUTHDOMAIN="ABC12345"

   );

   CREATE TABLE WORK.ABCD_AP_LDC_010_V AS  ----<<< CREATES A WORK.TABLE IN SAS

   select *

   from connection to TERADATA

   (

       SELECT * FROM TERADATA_SCHEMA.ABCD_AP_LDC_010_V

   );

   execute (commit) by TERADATA; 

   execute (

    CREATE VOLATILE TABLE ABCD_AP_LDC_010_V AS  ----<<< CREATES A VOLATILE TEMPORARY TABLE IN TERADATA 

      (SELECT TOP 5 AB_ID

      FROM

    TERADATA_SCHEMA.ABCD_AP_LDC_010_V) WITH DATA

      ON COMMIT PRESERVE ROWS

    ) by TERADATA;

   execute (commit) by TERADATA;

   execute(TERADATA_SCHEMA.STORED_PROCESS(PARAMETERS));  -----<<< TO EXECUTE A STORED PROCESS/MACRO 

   disconnect from TERADATA;

   quit;