Replicate from SQL Anywhere to MSSQL

[VolkerBarth]

I have trying to implement replication from SQLA to MSSQL. Tried Proxy Table in SQLA and insert query is working fine. But for actual replication we need update and delete. For that I used INNER JOIN remote database with SQLA db, but it shows error "update operation attempted on non-updatable remote query". My Update query is below:

 UPDATE Test AS T 
    SET T.ID = S.accno
    FROM Test AS T
    INNER JOIN "DBA"."acc_ledgers" S WITH (NOLOCK)
    ON S.accno = T.ID
    WHERE T.ID <> S.accno

What is the problem in this? Can you suggest any solution or alternatives?

Thank you

[VolkerBarth]

For a clarification WHY this restriction holds with proxy tables (still with V17, AFAIK), and how to possibly get around that, see this older question...

[regdomaratzki]

1) Have you considered using MobiLink to synchronize the data between your SQL Anywhere and MS SQL Server database? It's a pretty proven technology IMHO, but my opinion is quite biased.

https://help.sap.com/docs/SAP_SQL_Anywhere/a09ffd5a93ff477cbcf9d9b1aa4d0801/9462b226f4ae48d1a62b16f5...

2) With respect to the query you've written if you're not going to use MobiLink, can you provide some more context?

• Is this code written in an update trigger?
• Which tables are local tables and which tables are defined as proxy tables?

Thanks, Reg

[former_SQLA_member1694936]

I tried MobiLink but couldn't make it to work. My requirement is, source database will in SQL Anywhere and target in MSSQL. Data should be replicated to MSSQL. I am new to this SQL Anywhere and MobiLink and couldn't understand those well. Can you guide me to do this? Thank you

[VolkerBarth]

See this older question with some aspects to clarify whether MobiLink or "direct data access" via proxy tables etc. are better fits...

Your very general statement "source database will in SQL Anywhere and target in MSSQL. Data should be replicated to MSSQL." is much too vague in my humble opinion to give any hints...

(FWIW, you had posted your identical question there, too, and I had deleted that one in order to prevent duplicate questions...)