|
Hi In a 3rd party integration szenario I have the following challenge: On 1 Sybase server, there are 3 ERP companies stored in 3 dbs. The table structures on these 3 dbs are 100% identical. In my application I would like to show data from all 3 companies together in 1 view. Let's say, I want to see addresses from all 3 companies. Is there a way to create a view containing data from more than 1 db (meaning from more than 1 company)? Tanks in advance, your help is greatly appreciated. Heinz |
|
Hello Heinz, not sure if I got your question right, but if:
You may create a remote server entry for each of your DBs that is not local, and then a proxy table for each of these tables. It'll probably be helpful to use a different owner for each remote user which will allow you to use identical sets of proxy user names. Finally, you create a view unioning (UNION ALL) the proxy tables (and a local table, if required). It's probably a good idea to add a column with a literal value unique to each branch of the union indicating the origin / physical location of the rows. HTH Volker Hi Volker Thank you for your answer. I probably don't have too many administrative rights on that Sybase server. If I got your answer right, there is nothing like "just a view" containing the rows of all companies? The DBs are all local on the same machine in the same database engine (in sql server this would equal to "in the same instance"). So maybe or should I say hopefully no need for remote servers? And yes, I this works, I will add a column as an indicator per row. Thanks much for clarifying. Heinz
(10 May '19, 13:42)
DbWizard
Replies hidden
1
That's one probably the most fundamental difference between SQL Anywhere and MSSQL (or ASE). You connect to a single database, not to a server. Connectionwise, databases on the same server are just as separate as they would be on different servers. At the same time, you don't need administrative rights on the server, but the specific rights to create proxy objects in the database holding them plus credentials for the database you try to access via proxy. HTH Volker
(10 May '19, 14:29)
Volker DB-TecKy
Ouch, seems to be my misconception ;-( When reading about possible solutions, I found this post on Stackoverflow. https://stackoverflow.com/questions/31113458/how-to-join-tables-in-different-database-on-the-same-sybase-server Made me thinking it should be possible to just change the DB within the same connection. Just out of curiosity: Whats going on in the following snippet? Do they change the DB anyway, or do I misundertand the Term DB? select tabA.*,tabC.* from DatabaseB..TableA tabA, DatabaseA..TableC tabC where tabA.xxx = tabC.xxx Thanks again to bring some light into it. Very appreciated Heinz
(10 May '19, 15:53)
DbWizard
Replies hidden
The Stackoverflow is about SAP (formerly Sybase) Adaptive Server Enterprise aka ASE. If you're using ASE, it works precisely like that, but it would mean that you're asking in the wrong forum. If you're using SAP (formerly Sybase) SQL Anywhere, you're in the right place here, but the Stackoverflow post doesn't match your environment. HTH Volker
(10 May '19, 16:08)
Volker DB-TecKy
Just to add to ASE: If you are familiar with MS SQL, ASE is in many respects rather similar because both share a common origin...
(10 May '19, 19:46)
Volker Barth
I was not aware about the difference between this 2 products. But a least, I'm in the right forum here. Thanks for sharing your knownledge!
(13 May '19, 02:32)
DbWizard
|
|
Here are some more questions with samples how to access tables from other SQL Anywhere databases: I'm sure there are several more tagged woth "remote-data-access" or "proxy-tables". As to the UNION ALL statement: In case performace suffers (this is sometimes a problem with remote data), it's a common strategy to copy remote data to the "local" database, say to a (possibly local temporary table) via INSERT ... SELECT FROM myProxyTable, and then to run the UNION ALL on the local copy. |