Archives of the TeradataForum

Message Posted: Mon, 17 Mar 2003 @ 18:48:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I don't think the error is because of temp resources, that was just a comment about other side effects of stored procedures. IIRC, SP's were first supported in 4.0.0 so they aren't as mature / tuned as running shell scripts with BTEQ.

We could never solve the problem with the large stored procedures, I don't have any notes because it was at a prior job. We had a bunch of big sp's (1000's of lines) in the original job stream when we migrated to TD. Everything worked under 4.0.0 but we couldn't get the big procedures to save when we wanted to do an upgrade (I think we tried 4.03 but I'm not sure, it could have been 4.1.0). I sounds like you are getting past that point, we were getting errors when we tried to save the stored procedures.

I'd recommend doing the work in bteq rather than stored procedures (assuming it's a batch job instead of some application logic). That's probably not feasible in a short time frame so try breaking it down into smaller pieces. You need to put alot of logging statements because TD doesn't give you much visibility inside sp's (it's either successful or not, and the errors weren't very useful). This brings back bad memories because I remember running the sp's one statement at a time (out of 100's) to figure out where the errors were occurring. Eventually we logged essentially every query, that also would tell us where to restart abended jobs and it helps with performance tuning.

You might try putting the sp in a text editor (I use ultraedit) to see if you have any weird characters. I vaguely recall having weird syntax problems (if you leave an empty line in the middle of a statement or don't have a space at the end of a line before CR?).

	<Prev	Next>		<<First	<Prev	Next>	Last>>