Using SQLRemote with a SA12 consolidated database and remote databases with a mix of SA12 and ASA9, we have an issue with altering columns.

SQL executed at consolidated SA12 db (we're using MODIFY syntax to support ASA9):

ALTER TABLE dba.table_name MODIFY column_name NOT NULL;

The alter statement is run in passthrough mode to ensure it's also being replicated to all remote databases.

In a ASA9 db the statement fails, reason that the MODIFY syntax is now changed to

ALTER TABLE dba.table_name ALTER column_name NOT NULL;

ASA9 is not compatible with this syntax, and requires the MODIFY keyword. Is there any way of not having the sql changed from modify to alter syntax?

asked 15 Jul '13, 01:22

OBR's gravatar image

OBR
26691020
accept rate: 0%

edited 15 Jul '13, 02:01


We had the same issue lately, and the only way around I'm aware of is to use dynamic SQL, i.e. you do not send the original DDL statement to the remotes but send a (temporary) stored procedure that does issue the DDL statement locally (and thereby in the local version-specific dialect...), call that SP and drop if afterwards.

OK for smaller changes, possibly nasty for lots of ALTER statements....

Something like

PASSTHROUGH FOR SUBSCRIPTION TO <...>;

create procedure STP_TempUpdate()
begin
   execute immediate 'ALTER TABLE dba.table_name MODIFY column_name NOT NULL;';
end;
call STP_TempUpdate();
commit;
drop procedure STP_TempUpdate;

COMMIT;
PASSTHROUGH STOP;
permanent link

answered 15 Jul '13, 03:56

Volker%20Barth's gravatar image

Volker Barth
30.6k306456662
accept rate: 32%

FWIW, I see that I had used that case to ask for a simpler approach here (though still answered):

Add an option to SQL Remote's passthrough mode to leave syntax unmodified

(15 Jul '13, 03:59) Volker Barth

excellent suggestion! I presume the option in passthrough mode is not yet implemented (sa12). Your workaround should do the trick, but I do prefer this as a passthrough mode setting.

(15 Jul '13, 04:36) OBR
Replies hidden

I presume the option in passthrough mode is not yet implemented (sa12).

Given the fact that there has not been any comment on that suggestion (and particularly not a comment by Sybase engineers like Reg), I would think it is not even planned to implement that feature...

(15 Jul '13, 04:43) Volker Barth

Speaking as a person who happily supports clients running on 5.5, I do have say this: the two alternatives to dealing with a problem are "fix it or FAQ it". In this case, "FAQ it" would mean adding an entry to the V12 Behavior Changes section for SQL Remote, which I support for two reasons: V9 isn't supported by Engineering any more, and a workaround exists.

Not offended yet? Well, try this: Workarounds are a fact of life. If you have a workaround that keeps antique remotes alive in 2013, be proud! (but don't ask Netflix to deliver Orange Is The New Black on VHS :)

(15 Jul '13, 06:41) Breck Carter
Replies hidden

Your good advice is accepted, of course:)

However, the suggestion was no meant to fix SQL Remote for older versions (and I bet a fix that would change behaviour in v12 is not going to happen), it should help to avoid this kind of problems in coming version, say v16/v17 with v11/v12 remotes. - Just ask yourself how the new v16 DDL for the new role-based security model will be sent to remotes (or how it will be received there...) - Experience has shown that even attempts to omit "new syntax" might fail as the sent statement might nevertheless contain new syntax under the covers.

(15 Jul '13, 07:30) Volker Barth

But in the end, yes, yes, an existing workaround is sufficient to solve a current problem:)

"A bird in the hand is worth two in the bush." (or, as German speakers use: "Der Spatz in der Hand ist besser als die Taube auf dem Dach.").

(15 Jul '13, 07:35) Volker Barth

Oh, don't get me started on the new role-based security model, with its unintended consequences :)

(15 Jul '13, 08:02) Breck Carter
1

"help to avoid this kind of problems in coming version" - an excellent point, perhaps defeating my argument altogether. SQL Remote is a wonderful product, and supporting ancient remotes has always been one of its hallmarks, so this insistence on upselling, er, upgrading passthrough syntax seems an oversight that should be corrected.

"No, I do NOT want fries with my burger!"

Of course, I'm being hypocritical... when Foxhound displays CREATE TABLE syntax for a 5.5 database, it doesn't stick to 5.5 syntax :)

(15 Jul '13, 08:09) Breck Carter
Comment Text Removed
showing 3 of 8 show all flat view
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:

×412
×98
×90
×78

question asked: 15 Jul '13, 01:22

question was seen: 1,117 times

last updated: 16 Jul '13, 01:49