Attempting to do a Alter Subscription statement to change the IP address. I get an error stating it can't recognize the subscription name Any suggestions? Thanks

asked 15 Jun '11, 13:30

MileHighBob's gravatar image

MileHighBob
46224
accept rate: 0%

edited 11 Jan '14, 07:58

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109

The following two samples (using v12.0.1 build 3354) that change the address from localhost to rdomarat-w7e worked without error for me :

create synchronization user "rem1";
create synchronization subscription to p1 for "rem1"
   type 'tcpip' address 'host=localhost' script version 'v12';
alter synchronization subscription to p1 for "rem1" address 'host=rdomarat-w7e';

create synchronization user rem2;
create synchronization subscription MySyncSub to p1 for rem2 
  type 'tcpip' address 'host=localhost' script version 'v12';
alter synchronization subscription MySyncSub address 'host=rdomarat-w7e';
(15 Jun '11, 15:02) Reg Domaratzki

What version and build number of SQL Anywhere are you using?

What exact commands are you executing?

(15 Jun '11, 15:03) Reg Domaratzki

ALTER SYNCHRONIZATION SUBSCRIPTION TO kokolink TYPE TCPIP ADDRESS 'host=hg.nspirehealth.com;port=443';

Build 10.0.1.3887 I get SQLCode = -767

(15 Jun '11, 15:11) MileHighBob

You are trying to alter the settings for the kokolink publication, but the error is being thrown because you have never created the settings for the publication. I will get the error you specified if I execute the following SQL :

CREATE PUBLICATION p1 ( TABLE t1 );
CREATE SYNCHRONIZATION USER u1;
CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR u1 
  TYPE TCPIP ADDRESS 'host=localhost' OPTION sv='v1';
ALTER SYNCHRONIZATION SUBSCRIPTION TO p1 ADDRESS 'host=10.1.1.1';

However, the SQL below, which is equivalent, does not produce the error.

CREATE PUBLICATION p2 ( TABLE Admin );
CREATE SYNCHRONIZATION USER u2;
CREATE SYNCHRONIZATION SUBSCRIPTION TO p2 
  TYPE TCPIP ADDRESS 'host=localhost';
CREATE SYNCHRONIZATION SUBSCRIPTION TO p2 FOR u2 OPTION sv='v1';
ALTER SYNCHRONIZATION SUBSCRIPTION TO p2 ADDRESS 'host=10.1.1.1';

In the 2nd sample, we defined some defaults for the publication, and all future synchronization subscriptions to this publication inherit these default settings.

You probably want to add the "FOR user_name" clause to your ALTER SYNCHRONIZATION SUBSCRIPTION command to specify the name of the synch user as well.

(15 Jun '11, 15:33) Reg Domaratzki

Hello A couple of points 1)the remote computers currently sync with our server, so there has to be a subscription/publication 2) The reason there is no for clause is we need to change 100's of remote computers

(15 Jun '11, 15:40) MileHighBob

Can you please post the contents of the SYS.SYSSYNC table on the database where you are getting this error?

(15 Jun '11, 15:42) Reg Domaratzki

I was not allowed to attach a file so here it is:

sync_id type publication_id progress site_name option server_connect server_conn_type last_download_time last_upload_time created log_sent generation_number extended_state 1 D 77 TCPIP 00:00.0 0
2 D 1 3005349 77 memory=2m;LockTables=off;FireTriggers=on;SendTriggers=on;SendDownloadACK=OFF;st=on;sa=on;verbose=on; host=216.183.117.242;port=443;trusted_certificates=C:Program FilesKokoLinkpdsApp3mobilink_public_cert.cer HTTPS 10:45.2 10:45.2 1879363 3005349 1

(15 Jun '11, 15:52) MileHighBob

The format is un-readable. Can you edit your post so I can see what values are in which column for each of the two rows in the output?

(15 Jun '11, 15:59) Reg Domaratzki

It is in csv format. How would you like it won't allow me to attach a excell spread sheet

(15 Jun '11, 17:55) MileHighBob

sync_id type publication_id progress site_name option server_connect server_conn_type last_download_time last_upload_time created log_sent generation_number extended_state 1 D 77 TCPIP 00:00.0 0
2 D 1 3005349 77 memory=2m;LockTables=off;FireTriggers=on;SendTriggers=on;SendDownloadACK=OFF;st=on;sa=on;verbose=on; host=216.183.117.242;port=443;trusted_certificates=C:Program FilesKokoLinkpdsApp3mobilink_public_cert.cer HTTPS 10:45.2 10:45.2 1879363 3005349 1

(15 Jun '11, 18:01) MileHighBob

To attach a file, go up to your original question and click on the "edit" link below the question - This will let you change the text of the question. Once you do this you should see a row of icons above the text box - "B", "I", "globe", "double quotes", ... "paper clip". Click on the paper clip - this will then prompt you to select the file that you wish to attached. Browse to select the file and click ok (or attach?) You can then enter a description of your edit (e.g. "attached csv file") and then click "Save edit" button.

(15 Jun '11, 20:03) Mark Culp
1

You need a minimum of 100 reputation before you can upload a file.

(15 Jun '11, 20:38) Graeme Perrow
More comments hidden
showing 5 of 12 show all flat view

In order to change the MobiLink address for this remote database, you will need to execute the following command :

ALTER SYNCHRONIZATION SUBSCRIPTION TO kokolink FOR "77"
  TYPE HTTPS
  ADDRESS 'host=[newIPAddress];
           port=443;
           trusted_certificates=[fullPath]\mobilink_public_cert.cer';

Replace [newIPAddress] with the new ip address of the ML Server, and replace [fullPath] with the full path to your public certificate.

You will need to execute a different ALTER SYNCHRONIZATION SUBSCRIPTION command on each remote database, since you've defined a different synchronization user at each location.

permanent link

answered 16 Jun '11, 09:49

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.4k33578
accept rate: 40%

edited 16 Jun '11, 12:47

Hello I made the change I now get syntax error near 77 on line 1 Also you are saying I will need to create 2000 separate packages to push out this change to each lap top ouch!

ALTER SYNCHRONIZATION SUBSCRIPTION TO kokolink FOR 77 TYPE HTTPS ADDRESS 'host=host=hg.nspirehealth.com; port=443; trusted_certificates=c:program fileskokolinkpdsApp3mobilink_public_cert.cer';

(16 Jun '11, 10:42) MileHighBob
1

AFAIK, userids starting with digits have to be put in double quotes, so try

... TO "77" ...

[Source: Identifies definition]

(16 Jun '11, 10:45) Volker Barth
1

That did it! I had done this before I started the blog and forgot the "77" thanks for your help.

(16 Jun '11, 10:49) MileHighBob
1

Sorry, forgot numeric identifiers needed double quotes. Thanks Volker.

You could probably send a single package that creates, then executes and then drops a stored procedure that looks something like (syntax not checked, but you should get the general idea ) :

create procedure ChangeIP () 
begin
  declare @mluser varchar(128);
  declare @newaddr long varchar;
  declare @sql long varchar;
  select site_name into @mluser,
         REPLACE( server_connect,
                  '216.183.117.242', 
                  'new.tcp.ip.addr' ) into @newaddr
    from SYS.SYSSYNC 
   where site_name is not null 
     and publication_id = ( select publication_id 
                              from SYS.SYSPUBLICATION 
                             where publication_name = 'kokolink' );
  set @sql = 'ALTER SYNCHRONIZATION SUBSCRIPTION TO kokolink ';
  set @sql = @sql || 'FOR "' || @mluser || '" ';
  set @sql = @sql || 'TYPE HTTPS ';
  set @sql = @sql || 'ADDRESS ''' || @newaddr || '''';
  message 'EXECUTING : ' || @sql;
  execute immediate @sql;
end;

call ChangeIP();
drop procedure ChangeIP;

The general idea being that you dynamically figure out the name of the synchronization user that is subscribed to the publication in question, and also get the current address being used, replacing the old IP address with the new IP address.

(16 Jun '11, 11:07) Reg Domaratzki
Replies hidden

Executing this procedure manually on the clients works, however when i send this using a passthrough script it generates the following error Passthrough script 'test3 failed with SQL code -767. Cannot find synchronization subscription with the name 'imobpub' Any idea why this only works manually?

(09 Jan '14, 03:40) DIMITRIDEPROFT
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:

×295
×14

question asked: 15 Jun '11, 13:30

question was seen: 1,482 times

last updated: 11 Jan '14, 07:58