Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I am trying to compare and update, insert, or delete to a remote MSSQL database. Connected MSSQL database to SQL Anywhere with remote server, and added a proxy table. Successfully inserted data from local table to remote proxy table with following query:

   INSERT INTO Test
      (ID,Name)
      SELECT SOURCE.accno, SOURCE.particulars
      FROM   "DBA"."acc_ledgers" SOURCE WITH 
      WHERE  NOT EXISTS (
                           SELECT  *
                           FROM    Test TARGET
                           WHERE   TARGET.ID = SOURCE.accno
                         );
    COMMIT

I tried the same with update but it's showing error as :

Updated operation attempted on non-updatable remote query

My update query is:

UPDATE Test
SET A.Name = B.particulars
FROM Test as A,"DBA"."acc_ledgers" as B WHERE A.ID = B.accno

how to achieve this?. I am trying this for a long time, but didn't get any solution. Please help.. Thank you.

asked 27 Aug '22, 08:25

faheemmcfc's gravatar image

faheemmcfc
11223
accept rate: 0%

I still suggest that MobiLink is the solution to this requirement.

It is going to be very difficult to construct a single statement to solve this you cannot join a local table and a proxy table. And IMO moving rows to and from the remote is not a great idea from a performance perspective.

Have you considering trying this from the MSS side with its Linked Server (remote server) functionality. I am not sure that MSS allows joins on remote tables but perhaps it does.

(28 Aug '22, 18:31) Chris Keating

I like to use MobiLink, but I cannot get any proper guidance. Can you guide me. I cannot use SQLA as linked server in MSSQL, because I am using MSSQL in a shared hosting, hence there is no permission for linked servers. It will be a great help if you can guide me with MobiLink.. Thank you

(29 Aug '22, 02:26) faheemmcfc
Replies hidden

I can provide a very simple sample to get you started with MobiLink and MS SQL Server.

Am I correct in assuming that you only want to synchronize data one way from SQL Anywhere to MS SQL Server? The data in question will only every be inserted/updated/deleted in the SQL Anywhere database and you want those changes sent to MS SQL Server? Nobobdy will every connect to the MS SQL Server database and change the data that came from the SQL Anywhere database through MobiLink?

Thanks, Reg

(29 Aug '22, 10:41) Reg Domaratzki

That will be a great help. I need to replicate from SQLA to mssql only, that is only one way. there will be no changes made to mssql db.

(29 Aug '22, 13:51) faheemmcfc
Replies hidden

Just to clarify, are you able to add MobiLink catalog schema to the MSS database which is needed by MobiLink? See <sainstall>\MobiLink\Setup\syncmss.sql.

(29 Aug '22, 15:58) Chris Keating

Step One : Create the MobiLink system tables in your MS SQL Server consolidated database by running %SQLANY17%\MobiLink\Setup\syncmss.sql from your SQL Anywhere install.

Step Two : Create a DSN on the machine where SQL Anywhere is installed to connect to the MS SQL Server instance using the same user that you used in step one to create the MobiLink system tables.

Step Three : Start the MobiLink Server on the machine where SQL Anywhere is installed with a command line similar to the one below.

mlsrv17 -x http(port=8022) -zu+ -c "dsn=your_dsn";

Step Four : Execute the following SQL against the MS SQL Server instance using the same user that you used in step one to create the MobiLink system tables.

create table Admin (
  admin_id bigint not null primary key,
  data varchar(64) null
)
go

exec ml_add_table_script 'ml17_dbmlsync_mss', 'Admin', 'upload_insert',
'INSERT INTO Admin VALUES ( {ml r.admin_id}, {ml r.data}, getdate() )'
go

exec ml_add_table_script 'ml17_dbmlsync_mss', 'Admin', 'upload_update',
'UPDATE Admin SET data = {ml r.data} WHERE admin_id = {ml r.admin_id}'
go

exec ml_add_table_script 'ml17_dbmlsync_mss', 'Admin', 'upload_delete',
'DELETE FROM Admin WHERE admin_id = {ml r.admin_id}'
go

exec ml_add_table_script 'ml17_dbmlsync_mss', 'Admin', 'download_cursor',
'--{ml_ignore}'
go

exec ml_add_table_script 'ml17_dbmlsync_mss', 'Admin', 'download_delete_cursor',
'--{ml_ignore}'
go

Step Five : Execute the following SQL against your SQL Anywhere database.

create table Admin (
  admin_id bigint default autoincrement primary key,
  data     varchar(64)
);

create publication p1 ( table Admin );
create synchronization user "rem1";
create synchronization subscription to p1 for "rem1" 
  type 'http' 
  address 'host=localhost;port=8022' 
  option sv='ml17_dbmlsync_mss';

Step Six : Execute dbmlsync against your remote database, with a command line similar to the one below.

dbmlsync -c "eng=rem1;uid=dba;pwd=sql" -mp sp1 -o rem1.txt -k
(29 Aug '22, 16:34) Reg Domaratzki

To better understand what you did in the above steps, I would again suggest reading the "MobiLink - Getting Started" documentation, in particular the section entitled "MobiLink Synchronization".

https://help.sap.com/docs/SAP_SQL_Anywhere/a09ffd5a93ff477cbcf9d9b1aa4d0801/9462b226f4ae48d1a62b16f57209dfdd.html

While we don't have a tutorial specific to using MS SQL Server, I do believe that following the "Tutorial: Introducing MobiLink" tutorial, which sets up synchronization between two SQL Anywhere database will also help give you a solid understanding of the technology.

https://help.sap.com/docs/SAP_SQL_Anywhere/a09ffd5a93ff477cbcf9d9b1aa4d0801/81b92a486ce21014b57cd24cbd4ccbec.html

(29 Aug '22, 16:46) Reg Domaratzki

I didn't understand this. I am not familiar with this and also I am beginner in this.

(30 Aug '22, 04:55) faheemmcfc
showing 5 of 8 show all flat view

This is a task best suited for MobiLink, not proxy tables. Please see the comments on your initial question for further help in using MobiLink.

permanent link

answered 29 Aug '22, 17:02

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

I checked that before, but I cannot get a clear picture. I didn't understand how to connect mssql DB in that. Please help me. I need this solution so much. Thank you

(30 Aug '22, 04:54) faheemmcfc
Replies hidden
2

I'm not sure how else I can help. The forum, or any community for that matter, is great for asking a specific question and getting an answer, but comments along the lines of "cannot get a clear picture" will typically get answered with links to the documentation, which is what I will do again.

I would again suggest reading the "MobiLink - Getting Started" documentation, in particular the section entitled "MobiLink Synchronization".

https://help.sap.com/docs/SAP_SQL_Anywhere/a09ffd5a93ff477cbcf9d9b1aa4d0801/9462b226f4ae48d1a62b16f57209dfdd.html

While we don't have a tutorial specific to using MS SQL Server, I do believe that following the "Tutorial: Introducing MobiLink" tutorial, which sets up synchronization between two SQL Anywhere database will also help give you a solid understanding of the technology.

https://help.sap.com/docs/SAP_SQL_Anywhere/a09ffd5a93ff477cbcf9d9b1aa4d0801/81b92a486ce21014b57cd24cbd4ccbec.html

I will again suggest that on the machine where you have SQL Anywhere installed, you follow the "Tutorial: Introducing MobiLink" tutorial to setup synchronization between two SQL Anywhere databases. If you run into trouble during the tutorial, ask a specific question about the specific problem you are having in the tutorial, and it will almost certainly be answered.

Reg

(30 Aug '22, 08:53) Reg Domaratzki
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:

×103
×78
×70
×41
×5

question asked: 27 Aug '22, 08:25

question was seen: 628 times

last updated: 30 Aug '22, 08:53