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 :
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. |
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. 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". 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. 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
|
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.
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
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
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.
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.
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.
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.
Step Five : Execute the following SQL against your SQL Anywhere database.
Step Six : Execute dbmlsync against your remote database, with a command line similar to the one below.
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
I didn't understand this. I am not familiar with this and also I am beginner in this.