We are upgrading sqlremote replicating databases from 9.0.2 to 12.0.1. During the process which includes 100 + remotes located throughout the world we are replicating between 9.0.2 remotes and the converted 12.0.1 consolidated. No problems so far. The process of upgrading all remotes will take 1 to 2 years due to availability.

During this period we need to continue operations which includes passthrough modes for application updates. The problem is when passing through the 9.0.2 statement INSERT INTO ABC.XYZ ON EXISTING UPDATE ASA 12 changes this to INSERT INTO ABC.XYZ ON EXISTING UPDATE DEFAULTS OFF which then fails on syntax error when applied at the 9.0.2 remote.

"SQL statement failed: (-131) Syntax error near 'defaults' on line x" "Skipping"

asked 28 Mar '12, 19:04

J%20Diaz's gravatar image

J Diaz
830253144
accept rate: 14%

1

Reg,

Thanks very much I was hoping there may be a database option which turns off the syntax changes for passthrough statements. We are working around this problem as you suggest, however the effort is significant on large passthrough statements. Passing through the syntax used seems a good option since the ASA 12 server will automatically alter the syntax when applied as needed. We also ran across this issue with the modify vs. alter syntax on a table change.

Thanks again

Jim Diaz

(29 Mar '12, 18:00) J Diaz
Replies hidden

FWIW, after similar experiences, I dared to make an according product suggestion here...

(29 Jan '13, 08:37) Volker Barth

You'll often run into problems when syntax changes from one version to another and you are trying to execute statements in passthrough on version "X" that will be executed on version "Y". The problem is even worse with the INSERT ON EXISTING statement, since the database engine is adding the defaults clause into the transaction log no matter what.

While not ideal, you can work around the problem by changing your passthrough script from :

passthrough for subscription to cons.p1;
insert into cons.Admin on existing update values ( 20, 'Reg' );
insert into cons.Admin on existing update values ( 21, 'Amy' );
passthrough stop;

to :

passthrough for subscription to cons.p1;
create procedure drop_me()
begin
  execute immediate 'insert into cons.Admin on existing update values ( 20, ''Reg'' )';
  execute immediate 'insert into cons.Admin on existing update values ( 21, ''Amy'' )';
end;
call drop_me();
commit;
drop procedure drop_me;
passthrough stop;

By putting the SQL into a string and doing an execute immediate (which can only be done in an SP), you are preventing the database engine from parsing the string and changing the insert statement to include the DEFAULTS clause in the transaction log.

permanent link

answered 29 Mar '12, 11:01

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.4k33578
accept rate: 40%

1

oooo, excellent suggestion! kudos!

(29 Mar '12, 11:08) Breck Carter
Replies hidden

...If we could only offer some bounties:)

(29 Mar '12, 11:37) 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:

×85
×44
×32

question asked: 28 Mar '12, 19:04

question was seen: 1,441 times

last updated: 29 Jan '13, 08:37