Get access to tables in a SQL Server database from a SQL Anywhere database

[Former Member]

Hello

I think my title describes what I want to do.

I have succeeded the other way around with linked server, but now I need to access tables in a SQL Server database from a SQL Anywhere database. I have found information that says it is possble but not how you actually can do it, so I would be happy if someone could help..

(SQL Server 2014 and SQL Anywhere 16)

Thanks in advance

MG

[VolkerBarth]

There are several FAQs on this topic, look for "Remote data access", "proxy table" and the like.

Breck has published several blog articles on SA/MS SQL accesses, one of them related to proxy tables is here:

"Downloading Data into a SQL Anywhere In-Memory Database from Microsoft SQL Server" - Proxy Tables

(IMHO, using proxy tables within SA to access MS SQL Server tables/views is wayyyyyyy easier than the opposite via Linked Server.)

[Former Member]

so there is no other way then to 'create existing table' for each table in the SQL Server database? (if you want access to all tables)

[Breck_Carter]

That is correct. There is no way to "attach" an existing SQL Server database to a SQL Anywhere server and immediately have all those new tables visible in the SQL Anywhere catalog. AFAIK that feature is not available to ANY pair of different products X and Y.

But... see the sa_migrate_create_remote_table_list and sa_migrate_create_tables system procedures.

[VolkerBarth]

No, you can also use the FORWARD TO statement or the sp_forward_to_remote_server system procedure to access data from remote tables/views "natively", i.e. without having to create proxy tables.

For the latter, you may have a look at that other FAQ:

Can FOR loops be used with sp_forward_to_remote_server?.

But I would not claim that those facilities are easier to use than a bunch of proxy table definitions...

[Former Member]

well I got it to work the other way around (all the SQL Anywhere tables visable in SQL Server), and if tables change (additions of columns and so on) there is no way to update the Proxy table? You have to drop and create it again?

I will take a look at your links...

Thanks

[Former Member]

You seem to be missing the point of Proxy objects. They function as live connections to the real tables. No data is stored locally.

[VolkerBarth]

Yes, if the remote table schema is modified, you have to drop and re-create the proxy table (unless you just use a subset of the columns of the remote table which remains unchanged). - It's somewhat similar to "Linked tables" in MS Access, if you are familiar with that...

[Former Member]

I'm very aware that no data is stored locally, but there might have been some way that it could sense that the original table had been updated, and by running some type of command this update could also take place in the Proxy table....like a synchronization between the tables

[VolkerBarth]

it could sense that the original table had been updated

What do you mean by that? Data modifications (which are immediately "known" to the proxy table because it is just a "local view") or schema modifications (How would you expect SA to trace that?)?

FWIW: IMHO it would be more helpful if you would describe what you want to achieve instead of discussing (missing) features...

[Former Member]

I seem to have stepped on sore toes, a command to synchronize the tables does not sound strange (to me)

What I want to achieve is to be able to reach almost all SQL Server tables from SQL Anywhere except sys tables, now there seems to be several ways you can do this, so now I got to evaluate and see what suits us best

[VolkerBarth]

Please answer what you mean by "table updates" - see my previous comment...

("Synchronizing the tables" in SQL Anywhere would usually mean that data is synchronized, say with MobiLink, whereas proxy tables do access the remote data directly, so there is no need at all to "synchronize" data here. Therefore I'm asking whether you relate to "synchronizing schema changes".)

[Former Member]

I am not talking about the data in the table, i thought it was obvious because of my previous post that I meant adding or dropped columns in the table, perhaps i should have refered to update of table definition? update was perhaps not the best choice of word

[VolkerBarth]

OK, it's understood now.

Well, then you might possibly second the following (older) enhancement request:)

Altering a proxy table created with CREATE EXISTING TABLE

---

FWIW, it certainly would be possible to check whether the proxy definitions do fit the remote schema and to somewhat "automatically re-create" those proxy tables whose schema has been modified.

The system procedures sp_remote_tables() and sp_remote_columns() might help here.

[Breck_Carter]

MobiLink can be used to synchronize tables between SQL Server and SQL Anywhere. Synchronization of relational data is very simple, even trivial, until you implement it 🙂