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?
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....
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;
answered 15 Jul '13, 03:56