Undocumented Syntax Error

UDA
N/A

Undocumented Syntax Error

We have a stored procedure 2035 lines long.
We added 50 lines of code to this procedure and got the following syntax error when compiling:

SPL1077:E(L1827), Request larger than the maximum permissible size.

We then commented out a very large case statement and the program compiled.

Is there some sort of limit on the characaters/lines/complexity a stored procedure can have?

This is causing our developers significant concern.

ps: Further to this we have counted the number of characaters in this program and another large program.
The other program has compiled without this error message!
6 REPLIES
N/A

Re: Undocumented Syntax Error

I've found the following documentation:

The following are some important restrictions on the size and use of
stored procedures:
• The p****r limits apply if the SQL statements within a stored
procedure are large or
complex.

What does this mean? Really we need to know the actual constraint we are breaking. Then we can plan for future events of the issue.

Any input is greatly appreciated.
rgs
N/A

Re: Undocumented Syntax Error

You need to look at SQL Reference: Fundamentals, Appendix C: Teradata Database Limits

There you will find the following:

SQL request size in a stored procedure: 64 KB

That is what it is in 6.2 anyway.
N/A

Re: Undocumented Syntax Error

Yes and the limit of a direct piece of SQL in BTEQ/Queryman is 1Mb and the limit on a full sProc 64Mb.
However when I paste my SQL Statement into a text document it reads 40Kb on the surface.
Does Teradata think it's bigger under the covers? How can we see this?

We pasted it to a sProc of its own and it compiles fine. This doesn't tally up with the 64Kb limit.
This doesn't help our developers understand the issue any better.

We are running in Teradata Mode.
We are using SQL Assistant 7.0.0.
There are something like 620 columns in the insert statement.

And why are there limits on sProc size and SQL Statement size anyway?
rgs
N/A

Re: Undocumented Syntax Error

My guess is that the rest is being used up by the USING data:

From the Database limits section:

SQL Request size: (Includes SQL statement text, USING data,
and parcel overhead)
N/A

Re: Undocumented Syntax Error

Thanks, Excuse my ignorance but what exactly are the USING data & and parcel overhead?
Can we determine the size of this header info?
So really what we are actually left with is less than 64Kb for a SQL Statement within stored procedures.
SQLServer & Oracle don't have this limit. If you can have over 2000 columns on a table, there should be no limit in the complexity you can apply to those columns when Querying the table.
rgs
N/A

Re: Undocumented Syntax Error

Using data is all of the variable data that it has to submit along with your request text. If it is for an insert it would include the data for that insert.

Everything sent to the database is in the form of parcels. A parcel consists of a header and a body. The header says what the body is and its length. The body is the data, which in your case would be the request text.

See Database Administration documentation for more information. Do a search on parcel.