When using DML statements within a SQL Remote passthrough session with 12.0.1.4314, 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

insert into MyTable values ( 1, 'Value 1')
insert into MyTable values ( 2, 'Value 2')
...
go

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;

asked 28 Jan '16, 07:26

Volker%20Barth's gravatar image

Volker Barth
30.3k301452660
accept rate: 32%

edited 28 Jan '16, 08:23

Comment Text Removed

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

permanent link

answered 28 Jan '16, 09:36

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

OK, that's understood. So I guess older versions were less strict w.r.t. batches and delimiters...

(28 Jan '16, 09:57) Volker Barth

More correctly ... it is a documented limitation as well

(28 Jan '16, 10:15) Nick Elson S...
Replies hidden

Well, as stated, I was more surprised because it had worked with older versions like v8, and I'm quite sure the documented limitations have not changed in-between...

(28 Jan '16, 10:34) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×77
×28

question asked: 28 Jan '16, 07:26

question was seen: 274 times

last updated: 28 Jan '16, 10:34