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 |
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. 2) With respect to the query you've written if you're not going to use MobiLink, can you provide some more context?
Thanks, Reg 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
(26 Aug '22, 02:13)
faheemmcfc
Replies hidden
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...)
(26 Aug '22, 04:53)
Volker Barth
|
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".
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. 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
(05 Sep '22, 04:50)
faheemmcfc
Replies hidden
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 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?
(05 Sep '22, 05:43)
faheemmcfc
Replies hidden
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".
(05 Sep '22, 06:35)
Volker Barth
1
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).
(05 Sep '22, 23:02)
Reg Domaratzki
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?
(06 Sep '22, 07:19)
faheemmcfc
Replies hidden
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.
(06 Sep '22, 08:10)
Volker Barth
|
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...