|
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 |
|
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. 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" ...
(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
|
The following two samples (using v12.0.1 build 3354) that change the address from localhost to rdomarat-w7e worked without error for me :
What version and build number of SQL Anywhere are you using?
What exact commands are you executing?
ALTER SYNCHRONIZATION SUBSCRIPTION TO kokolink TYPE TCPIP ADDRESS 'host=hg.nspirehealth.com;port=443';
Build 10.0.1.3887 I get SQLCode = -767
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 :
However, the SQL below, which is equivalent, does not produce the error.
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.
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
Can you please post the contents of the SYS.SYSSYNC table on the database where you are getting this error?
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
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?
It is in csv format. How would you like it won't allow me to attach a excell spread sheet
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
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.
You need a minimum of 100 reputation before you can upload a file.