Is it possible to execute explicit SQL against a Teradata database to perform a CREATE/REPLACE PROCEDURE on the database? I am running into errors when I attempt to do this. The Teradata Stored Procedure was developed and tested in SQL Assistant and works without issue. When I move it over into SAS the 'REPLACE PROCEDURE...' Teradata SQL code portion fails. If I create the procedure using Teradata SQL Assistant I can successfully execute a 'CALL PROCEDURE();' statement using explicit pass through SQL. Below is a simplified version of what I am trying execute. The log first has this message twice:
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
followed by this Error:
ERROR: Teradata execute: Syntax error: Invalid SQL Statement.
proc sql noerrorstop;
connect to teradata(server="myserver" user="&usr" password="&pw" connection=global mode=teradata);
execute(
REPLACE PROCEDURE MYDB.my_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128))
SQL SECURITY INVOKER
BEGIN
IF EXISTS(SELECT 1 FROM DBC.TablesV WHERE DatabaseName =:DB_name AND TABLENAME = :TBL_name) THEN
CALL DBC.SysExecSQL('
INSERT INTO '||:DB_name||'.'||:TBL_name||'
SELECT
AU_NUM
, OUTLET_ID
, sale_wk
, COUNT(DISTINCT account) AS accounts
FROM
BMGU_TEMP.ddd_startright;' );
ELSE
CALL DBC.SysExecSQL('
CREATE MULTISET TABLE '||:DB_name||'.'||:TBL_name||' AS (
SELECT
AU_NUM
, OUTLET_ID
, sale_wk
, COUNT(DISTINCT account) AS accounts
FROM
mydb1.mytable
) WITH DATA
PRIMARY INDEX(AU_NUM, sale_wk);');
END IF;
END;
) by teradata;
The quoted text within both of the DBC.SysExecSQL() parts is considerably longer than what I have shown above, but I had to change the query to remove company information. Even though the text is within a execute() by teradata; statement and being passed to the DBMS, is it possibly an issue with quotes? Again, the entire REPLACE PROCEDURE... statement executes without issue on Teradata.
NOTE: Couldn't figure out how to turn off the emoji's, but anywhere there is an emoji should be :DB_Name
Thanks.
Interesting. I wouldn't think creating/replacing a procedure should be a problem. I've run all sorts of stuff in Oracle using explicit passthrough, and I don't think Teradata would be different. My guess is that it is a quoting problem, I've run into similar problems and they're always a pain to track down. Have you tried creating a much simpler procedure, ideally one without all the internal quoting, to see if that works? If so, then you'll know it's a quoting issue and can start adding parts back in piecemeal to try and track down what's going wrong.
I tried a much smaller/simpler procedure. I no longer get the note about long string/unbalanced quotes but I still get the error "
ERROR: Teradata execute: Syntax error: Invalid SQL Statement." In looking in the log and what is being executed by Teradata it appears to all by syntactically correct. There is some white space added due to tabs in my formatting/indenting of the procedure but I wouldn't think that is impacting anything. Here's the log excerpt:
78 proc sql noerrorstop;
79 connect to teradata(server="myserver" user="&usr" password="&pw" connection=global mode=teradata);
80 execute(
81 REPLACE PROCEDURE MYDB.my_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128))
82 SQL SECURITY INVOKER
83 BEGIN
84 IF EXISTS(SELECT 1 FROM DBC.TablesV WHERE DatabaseName = :DB_name AND TABLENAME = :TBL_name) THEN
85 CALL DBC.SysExecSQL('
86 INSERT INTO '||:DB_name||'.'||:TBL_name||'
87 SELECT TOP 50 ACCT_NUM, OPEN_DT
88 FROM DB1.ACCOUNTS;' );
89 ELSE
90 CALL DBC.SysExecSQL('
91 CREATE MULTISET TABLE '||:DB_name||'.'||:TBL_name||' AS (
92 SELECT TOP 50 ACCT_NUM, OPEN_DT
93 FROM DB1.ACCOUNTS
94 ) WITH DATA
95 PRIMARY INDEX(ACCT_NUM);');
96 END IF;
97 END;
98 ) by teradata;
TERADATA_0: Executed: on connection 0
REPLACE PROCEDURE MYDB.my_proc (IN DB_name VARCHAR(128), IN TBL_name VARCHAR(128)) SQL SECURITY INVOKER BEGIN IF
EXISTS(SELECT 1 FROM DBC.TablesV WHERE DatabaseName = :DB_name AND TABLENAME = :TBL_name) THEN CALL DBC.SysExecSQL(' INSERT INTO
'||:DB_name||'.'||:TBL_name||' SELECT TOP 50 ACCT_NUM, OPEN_DT FROM DB1.ACCOUNTS;' ); ELSE CALL
DBC.SysExecSQL(' CREATE MULTISET TABLE '||:DB_name||'.'||:TBL_name||' AS ( SELECT TOP 50 ACCT_NUM, OPEN_DT FROM
DB1.ACCOUNTS ) WITH DATA PRIMARY INDEX(ACCT_NUM);'); END IF; END;
DBMS_TIMER: summary statistics
DBMS_TIMER: total SQL execution seconds were: 0
DBMS_TIMER: dbiopen/dbiclose timespan was 0.
ERROR: Teradata execute: Syntax error: Invalid SQL Statement.
What do you have set for the SASTRACE options? I usually run my code with the following so any Oracle error messages are passed back to the SAS Log, that may help determine why Teradata doesn't like the code if you haven't already.
OPTIONS NOSTSUFFIX;
OPTIONS SASTRACELOC=SASLOG;
OPTIONS SASTRACE=',,t,dbs';
This is my normal settings for those options:
option debug=dbms_timers
sastrace=',,,d'
sastraceloc=saslog nostsuffix;
I took it a couple of steps farther to get more detailed info and went with this and re-ran and it only provided a little more information like the timing and some return codes. Not sure if there's a way to see what the return codes mean.
SASTRACE=',,dt,dsa';
FROM THE LOG:
ACCESS ENGINE: Entering dbiclose
TERADATA: trclose()
Summary Statistics for TERADATA are:
Total SQL execution seconds were: 0.002083
Total seconds used by the TERADATA ACCESS engine were 0.002773
ACCESS ENGINE: DBICLOSE open_id 0, connect_id 0
ACCESS ENGINE: Exiting dbiclos with rc=0X00000000
ACCESS ENGINE: Exiting execute with rc=0X8003180C
ERROR: Teradata execute: Syntax error: Invalid SQL Statement.
That's unfortunate, Oracle is usually quite helpful with error codes on stuff like this, but I've only used Teradata very briefly so don't know what else I can offer. The only other thing I'd suggest is to continue to pare down the code until you get a CREATE PROCEDURE that actually works, even if it's not really doing anything besides creating an empty procedure or something. Once you get something that actually runs, you can start adding in bits and pieces until the errors come back, which should tell you what's going wrong. Otherwise I guess you can open a SAS Support ticket, or maybe someone else will have better insight into Teradata specific troubleshooting.
Sorry I wasn't of more help.
I appreciate your responses and assistance. I'll probably first proceed with a SAS Support Ticket just to confirm if there's some limitation with the SAS/ACCESS Teradata preventing this. As I cannot see anything that would be preventing it from running. I can take the code that is sent to Teradata verbatim from the SAS log, paste into Teradata SQL Assistant and execute it without issue.
Derek - Did you get a response for this ? I am attempting the same thing.
Michael
Yes, but I wasn't able to validate if the suggestions worked due to security policy on my system and not being allowed to make changes to ODBC settings. Essentially, the SAS tech stated the problem was with Teradata SQL Assistant (This is what I was using to develop my SQL script) and a setting that impacts the query output. Here was the response from my ticket:
You need to go to the ODBC administrator
You can get to it doing this
Thanks! This helped immensely with debugging. Was setting a variable to SQL script and used EXEC() to execute but kept getting errors when using SAS Explicit Passthrough. When I set this, I could see that the SQL script assigned to the variables had missing spaces due to SAS not parsing ENTER as a space. This really helped me immensely.
@Schalk_Burger wrote:
Thanks! This helped immensely with debugging. Was setting a variable to SQL script and used EXEC() to execute but kept getting errors when using SAS Explicit Passthrough. When I set this, I could see that the SQL script assigned to the variables had missing spaces due to SAS not parsing ENTER as a space. This really helped me immensely.
Not sure what that means.
Did you perhaps have lines like this in the code:
'this is some long quoted string
that is too long for one line
of code'
You should clean up the code (whether or not you push it to Teradata via SAS) to not have single quoted strings that span lines.
'this is some long quoted string'
||' that is too long for one line'
||' of code'
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.