thanks - I do appreciate that the problem statement provided by the error message does rather suggest the solution :-)
Sadly, coming from an Oracle background & having scant knowledge of Teradata, I don't know how not to run the call in a multi-statement.
The documentation seems to be quite clear that the way to invoke a stored procedure is via the 'CALL' keyword. However this situation - one in which the stored procedure contains >1 DML statements seems to be an exception to the rule.
Let me rephrase my question:
Is there any way to run a single stored procedure containing >1 DML statements through a client tool such as BTEQ or SQL Assistant?
Hi Andrew, a Stored Procedure can only be called in the same session mode it was created, ANSI or Teradata, as there are some differences in transaction handling. You must switch to Teradata mode, compile it and then CALL it.
I usually don't work in ANSI mode, but i don't think it's related to your source code.
Try to commit before the CALL, but this shouldn't help.
Put the insert/update statements in a BEGIN REQUEST/END REQUEST block, but this shouldn't help, because it submits them as a multi-statement and your error is related to CALLing the SP itself, no the code inside the SP.
Remove both insert/update and see if it's still failing.
Hi Andrew, i just tried it, any CALL in ANSI mode fails for a .NET connection, when you switch to odbc it succeds.
Checking the query log i found its adding a COMMIT to the CALL as a multi-statement and this is not allowed.
I found this in the .NET help: "When the Session Mode is ANSI, do not set the CommandText to a CALL statement and CommandType to CommandType.Text. When this request is submitted to the Teradata database, a TdException will be thrown. The reason this occurs is that the provider is unaware that a stored procedure is to be executed and will append a COMMIT statement to the request. Teradata requires that the CALL statement be the last statement in a multi-statement request."
I don't know if this is a bug or a feature, i'll ask Mike Dempsey (the SQLA-developer) how to fix it.