When using DML statements within a SQL Remote passthrough session with 18.104.22.16814, these DML statements were rejected with SQLCODE -707 ("Statement is not allowed in passthrough mode").
Initially, I was surprised because we have eventually used such DML statements within passthrough mode in older versions without problems (*), and I wondered whether I had noticed a possible limitation in v12 I had not been aware of.
A closer look revealed that the error messaged appeared when two INSERT or UPDATE statements followed each other without a statement delimiter, such as
Adding a semicolon after each statement fixed the problem easily.
Outside passthrough mode, such DML statements are accepted without delimiters (as long as they remain unambiguous, of course), I guess as T-SQL syntax.
So is that need for a delimiter a known restriction in SQL Remote Passthrough mode?
(*) Aside: One might ask why we use DML statements in a passthrough session and do not simply run them "normally" and let SQL Remote distribute the values. - Well, it's handy when you are about to alter an existing, non-empty table to add a NOT NULL column and want to handle that within one passthrough session, such as
PASSTHROUGH FOR SUBSCRIPTION TO ...; SETUSER ...; ALTER TABLE MyTable ADD MyNewColumn INT NULL; -- can't be NOT NULL while empty UPDATE MyTable SET MyNewColumn = <whatever>; ALTER TABLE MyTable ALTER MyNewColumn NOT NULL; ... COMMIT; PASSTHROUGH STOP;
Without the semicolons, the 2 inserts are treated as a SQL Batch and that is the source of the issue you are seeing. Putting GO statements between and after them would also work to correct this.
In one sense this is covered by the description of that error
answered 28 Jan '16, 09:36
Nick Elson S...