Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to DBMS

[DerekD_WF]

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.

[Sven111]

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.

[DerekD_WF]

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.

[Sven111]

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';

[DerekD_WF]

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.

[Sven111]

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.

[DerekD_WF]

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.

[Tucky]

Derek - Did you get a response for this ? I am attempting the same thing.

Michael

[DerekD_WF]

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 

1. Select Start=>Run
2. Type in ODBC
3.  Select the ODBC administrator  
4.  Find the DSN you are working with in SQL assistant
5.  Select Configure
6.  Click on the Options button
7. Then select Disable Parsing.   

[Schalk_Burger]

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.

[Tom]

---

@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'