I have a version 12 consilidated database and a version 8 remote database. This has been working perfect for some time. However today I came across an issue that I need some suggestions.

I run this statement from consolidated database:

passthrough only for db_ver8;
alter table_1 modify column1 numeric(5,3);
passthrough stop;

It appears for version 12 the syntax changed slightly and the system is automatically sending the new syntax to the version 8 database which causes a syntax error.

statement skipped error near numeric.....

It appears the statement getting sent to the version 8 database gets changed to:

alter table_1 alter columnn1 numeric(5,3);

where the "modify" is getting changed to "alter"

Any suggestions??

Thanks TPS

asked 17 Sep '12, 15:02

TPS's gravatar image

TPS
196131320
accept rate: 0%

edited 17 Sep '12, 15:05

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118


When you execute DDL on the v12 database, the syntax that goes into the transaction log might be altered, and this is needed for proper recovery. This can cause issues when this DDL is executed in passthough and also sent down to remote databases whose version number doesn't recognize the altered syntax.

You can get around the issue by first creating a stored procedure (in passthough) that takes in a string and does an execute immediate on the string.

passthrough for subscription to p1;
create procedure ExecText( in @txt long varchar )
begin
  execute immediate @txt;
end;
passthrough stop;

Once that is done, you can execute any DDL using the stored procedure, and it won't be parsed by the database engine and posibly modified.

passthrough only for rem2;
call ExecText( 'alter table table_1 modify column1 numeric(5,3)' );
passthrough stop;
permanent link

answered 17 Sep '12, 15:57

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

Thanks... works perfect....

(18 Sep '12, 01:17) TPS
Replies hidden
1

Just to add: That FAQ does handle a very similar issue (and suggests the same solution)...

(18 Sep '12, 03:21) Volker Barth
Replies hidden

Please accept the answer, so others have it easier.

(18 Sep '12, 07:28) Martin
1

I spent a solid two minutes looking for that thread and didn't find it. Thanks Volker.

PS: I like the more general solution I proposed here, where we create an ExecText procedure for future use instead of a one-off procedure.

(18 Sep '12, 10:14) Reg Domaratzki
Comment Text Removed
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:

×78

question asked: 17 Sep '12, 15:02

question was seen: 2,109 times

last updated: 18 Sep '12, 10:29