Update remote database table with local data

[former_SQLA_member1694936]

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.

[chris_keating]

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.

[former_SQLA_member1694936]

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

[regdomaratzki]

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

[former_SQLA_member1694936]

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.

[chris_keating]

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.

[regdomaratzki]

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

[regdomaratzki]

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/9462b226f4ae48d1a62b16f5...

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/81b92a486ce21014b57cd24c...

[former_SQLA_member1694936]

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

[regdomaratzki]

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.

[former_SQLA_member1694936]

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

[regdomaratzki]

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/9462b226f4ae48d1a62b16f5...

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/81b92a486ce21014b57cd24c...

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