Question Re: Alter subscription in Technology Q&A

Alter subscription

Former Member — Wed, 15 Jun 2011 13:30:27 GMT

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

Re: Alter subscription

regdomaratzki — Wed, 15 Jun 2011 15:02:31 GMT

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';

Re: Alter subscription

regdomaratzki — Wed, 15 Jun 2011 15:03:27 GMT

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

What exact commands are you executing?

Re: Alter subscription

Former Member — Wed, 15 Jun 2011 15:11:12 GMT

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

Build 10.0.1.3887 I get SQLCode = -767

Re: Alter subscription

regdomaratzki — Wed, 15 Jun 2011 15:33:10 GMT

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.

Re: Alter subscription

Former Member — Wed, 15 Jun 2011 15:40:08 GMT

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

Re: Alter subscription

regdomaratzki — Wed, 15 Jun 2011 15:42:36 GMT

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

Re: Alter subscription

Former Member — Wed, 15 Jun 2011 15:52:45 GMT

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

Re: Alter subscription

regdomaratzki — Wed, 15 Jun 2011 15:59:44 GMT

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?

Re: Alter subscription

Former Member — Wed, 15 Jun 2011 17:55:25 GMT

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

Re: Alter subscription

Former Member — Wed, 15 Jun 2011 18:01:58 GMT

Re: Alter subscription

MarkCulp — Wed, 15 Jun 2011 20:03:47 GMT

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.

Re: Alter subscription

graeme_perrow — Wed, 15 Jun 2011 20:38:51 GMT

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

Re: Alter subscription

regdomaratzki — Thu, 16 Jun 2011 09:49:58 GMT

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.

Re: Alter subscription

Former Member — Thu, 16 Jun 2011 10:42:19 GMT

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';

Re: Alter subscription

VolkerBarth — Thu, 16 Jun 2011 10:45:57 GMT

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

... TO "77" ...

[Source: Identifies definition]

Re: Alter subscription

Former Member — Thu, 16 Jun 2011 10:49:40 GMT

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

Re: Alter subscription

regdomaratzki — Thu, 16 Jun 2011 11:08:00 GMT

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.

Re: Alter subscription

Former Member — Thu, 09 Jan 2014 03:40:04 GMT

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?