Replicate from SQL Anywhere to MSSQL

[former_SQLA_member1694936]

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...

[VolkerBarth]

FWIW, if you do not want to use MobiLink, you can also "update" remote data via proxy tables via one of these two options. Say, the local SQL Anywhere table is called LT ("Local table") and the proxy table to the MS SQL Database is called RT ("remote table"), both just having one PK column ID and an arbitrary column "Col".

• If there are no data modifications made in MS SQL, and there are no foreign keys in MS SQL on the according table, and FT is supposed to contain all rows of LT, you might simply delete data from FT and then re-insert the contents from LT into FT. This approach does not require an updatable join between local and proxy tables. (Of course, if this is a huge table, that simple approach might have negative performance implications.)

• Otherwise, you can update FT via single-row UPDATE statements based on the contents of a modified row from LT, again omiting an an updatable join.
  We have used that approach with the following (untested!) scheme that is based on a for loop over the rows of LT that are different from those in FT:

   begin
      for myLoop as myCrs insensitive cursor for
         select LT.ID as MyID, LT.Col as MyCol from LT
         except
         select ID, Col from FT
         order by LT.ID
      for read only    
      do
         if (select count(*) from FT where ID = MyID) = 0 then
            insert FT (ID, Col) values (MyID, MyCol);
         else
             update FT set Col = MyCol where ID = MyID;
         end if;
      end for;
   end;

If you have to apply DELETEs, too, you could use a similar approach to select those rows on FT whose IDs do not exist in LT anymore, and delete those rows in FT accordingly.

Basically, that's a "home-brewn" MobiLink approach. It might be worthwhile for the replicaton of one or a few tables but I would highly recommend MobiLink otherwise.

[former_SQLA_member1694936]

I have no problem with mobilink, but cannot understand how to do that. My main database will be in SQLA and MSSQL db will be remote. Data will be flowing from SQLA to MSSQL only. If this can be achieved with MobiLink I will be so happy. I will try your code today and reply the result here . Thank you

[former_SQLA_member1694936]

I tried your code but couldn't successfully run it, I will give my code below: begin for myLoop as myCrs insensitive cursor for select LT.accno as MyID, LT.particulars as MyCol from DBA.acc_ledgers LT except select ID, Name from Test order by LT.accno for read only
do if (select count(*) from Test where ID = MyID) = 0 then insert Test (ID, Name) values (MyID, MyCol); else update Test set Name = particulars where ID = MyID; end if; end for; end;

With this code I am getting an error as below: "Invalid ORDER BY specification". I cannot understand the error in order by.. Can you help me?

[VolkerBarth]

Well, then just leave out the ORDER BY, it's not necessary. (As stated, this is just untested code to give a hint.) I still don't know what table you are using as local and as proxy table, my sample is the best advice I can give here. (And I suspect the SET clause in your UPDATE statement is wrong as instead of "set Name = particulars" you would need to use the alias from the SELECT clause, so this probably should be "set Name = MyCol".)

---

As to MobiLink: As Reg and other experts from the SQL Anywhere team have proposed, please read the documentation and follow the samples and then possibly tell what problem you are facing. We cannot help much when you simply state you "cannot understand how to do that".

[regdomaratzki]

This can be achieved with MobiLink. If you read the documentation, your MSSQL database will be the "consolidated" database and SQL Anywhere will be the "remote" database. Any changes you make in the "remote" database (i.e. SQL Anywhere) will be synchronized to the "consolidated" database (i.e. MSSQL).

[former_SQLA_member1694936]

I tried MobiLink before but in that there is no option to use MSSQL database as remote database. My original DB is in SQLA and from that data will be synced to remote database of MSSQL. I tried many time but couldn't find a way to use mssql as remote database. Only SQL Anywhere and Ultralite supported as remote. Is there any other way?

[VolkerBarth]

That's a misunderstanding. As Reg has stated several times, conceptually you need to treat MS SQL Server as the consolidated database and SQL Anywhere as the remote. But that's no real restriction, as it does not matter at all whether you are making changes at the consolidated or the remote or on both - MobiLink can cope with changes on any site. Additionally, you can configure MobiLink to use "upload-only" synchronizations - that will fit your requirement that changes are only done on the SQL Anywhere site.

[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...)