topic Re: Unable to Call Stored Procedure from Teradata to SAS in SAS Programming

Unable to Call Stored Procedure from Teradata to SAS

siruvuri — Fri, 15 Dec 2017 14:34:59 GMT

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.

Re: Unable to Call Stored Procedure from Teradata to SAS

DaveBirch — Mon, 18 Dec 2017 00:52:24 GMT

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.

Re: Unable to Call Stored Procedure from Teradata to SAS

BonnieS100 — Fri, 26 Jul 2019 12:56:00 GMT

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;