How can I query two SQL Anywhere 12 databases? Both databases use the same database server name. I would like to do something like:
select * from SkyArtist.Hades.hades.invoices ih inner join SkyArtist.Periodicals.periodicals.invoices ip on ih.id = ip.hades_invoice_id
How can I run such a command in Interactive SQL? And, later on, as embedded SQL?
In the Sybase documentation I found something about linking to the other database (sp_addlinkedserver) but I don't understand it enough to get it to work. I am also not sure if this command means linking to a SQL Server, not an SQL Anywhere database.
Here's a snippet of template code...
CREATE SERVER ddd2_server CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 12;ENG=ddd2;DBN=ddd2'; /* or... CREATE SERVER ddd2_server CLASS 'SAODBC' USING 'DSN=ddd2'; */ CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere TO ddd2_server REMOTE LOGIN "DBA" IDENTIFIED BY 'sql'; CREATE EXISTING TABLE proxy_t AT 'ddd2_server...t'; /* or... CREATE EXISTING TABLE proxy_t ( pkey INTEGER NOT NULL, data INTEGER NOT NULL, PRIMARY KEY ( pkey ) ) AT 'ddd2_server...t'; */ SELECT * FROM proxy_t ORDER BY proxy_t.pkey;
answered 06 Sep '13, 17:21
The SELECT you have stated would work on a system like MS SQL Server or ASE (one database engine, one master database, several user databases).
With SQL Anywhere, you cannot access a second database (even if running on the same server) "directly" but with the help of Remote Data Access (aka "proxy tables").