select from two databases

I want to do this:

  other_table_count = (select count(
                                  from other_db_table odt
                                  where =
  this_db_table tdt

in Sybase Central's Interactive SQL, against an Adaptive Server Anywhere database.

The desired effect is that the count shows 1 if the current row's ID exists in the other DB and 0 if it does not.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

select count(*)
from this_db.table_name tdt, other_db.table_name odt
where =
group by
one slight change.

you need 2 dots between the database and table names (not one)
eg    FROM this_db..this_table

this signifies a dbo table, if the table is not 'dbo', then specify the owner between the dots
eg    FROM this_db.this_owner.this_table
yes chrisking - you are correct
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Syntax like that will not work with SQL Anywhere. There may be a way if you are using client-server version of SQL Anywhere. (i.e your db engine is dbsvr7.exe, not dbeng7.exe)
Here is the info from Sybase,

An Adaptive Server Anywhere server may have several local databases running at one time. By defining tables in other local Adaptive Server Anywhere databases as remote tables, you can perform cross database joins.

For example, if you are using database db1 and you want to access data in tables in database db2, you need to set up proxy table definitions that point to the tables in database db2. For instance, on an Adaptive Server Anywhere named testasa, you might have three databases available, db1, db2, and db3.

If using ODBC, create an ODBC data source name entry for each database you will be accessing.

Connect to one of the databases that you will be performing joins from. For example, connect to db1.

Perform a CREATE SERVER for each other local database you will be accessing. This sets up a loopback connection to your Adaptive Server Anywhere server

CLASS 'asaodbc'
USING 'testasa_db2'
CLASS 'asaodbc'
USING    'testasa_db3'
or using JDBC:

CLASS 'asajdbc'
USING 'mypc1:2638/db2'
CLASS 'asajdbc'
USING 'mypc1:2638/db3'
Create proxy table definitions using CREATE EXISTING to the tables in the other databases you want to access.

AT 'local_db2...employee'
It is pretty easy to do from SQL Central.

I tried it for yourself.

1) Start SQL Central and connect to db1.
2) Create a ODBC DSN to DB2
3) Look under SQL Remote folder, there is a Remote Servers folder.
Double click on it
4) Click on Add Remote Servers
5) give a name for remote server
6) select "Sybase Adaptivr Server Anaywhere"
7) select ODBC and specifiy the DSN name.
8) Take the defaults
9) Click on the remote server that is just got created.
10) There is a tab to add proxy tables.


Did that work?
PicklesAuthor Commented:
OK.. I'm on Sydney time here, so I've just started my working day.

Let me work through all the responses and I'll get back to everyone.
PicklesAuthor Commented:
OK.. addressing these in order,

1) Firstly  amitpagarwal with joining the two tables and ChrisKing's modification to include ownername:

It seems that the tables being referenced are all from the current connection.

As an example, if I simply execute something like:

select * from other_db..table_name odt

I actually get data from the currently connected database (ie the window in which I execute the statement in ISQL). It just ignores any reference to database names.

2) Next, namasi_navaretnam suggests the CREATE SERVER and CREATE EXISTING TABLE syntax. Creating the server works without problem and I have tried both 'asaodbc' and 'odbc'. When it comes to creating tables, however, I get this error:

ASA Error -656: Unable to connect to server 'local_db2': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Yet the DSN is recognised in the CREATE SERVER statement.

I checked my ODBC DSN settings and confirmed that I could connect to the remote database from the control panel... so I'm stumped there.

3) Then namasi_navaretnam made another suggestion, to go through a set of steps to create a proxy table from the Sybase Central interface.

All the steps worked until 'Create Proxy Table'. The first dialogue box asks for the server name (loacl_db2) and the database. I have tried leaving the database blank, and putting the database name in, and in both cases I get the following error:

ASA Error -265: Procedure 'sp_remote_tables' not found.


The problem is probably something obvious, which I have missed due to my inexperience - so any further suggestions are most welcome!


PicklesAuthor Commented:
PS. The DB Engine is dbsvr7.exe.

This is what you need to do to resolve 'DSN not found' error.

Defining  ODBC DSN to both databases

1) Start Sybase Central
2) Goto "Adaptive Server Anywhere 7/Utilities" folder
3) Double click on "ODBC Administrator"
4) Click on "System DSN" tab and then click on "Add" button
5) Select "Adaptive Server Anywhere" and then click on Finish.
6) On Data Source Name field type a name without spaces say "db1". This is the name ("db1") you will use later as the dsn or Data Source Name in the script/remote link definition
7) Type dba for user and sql for password
8) Under Database tab specify connection info. If client/server version then specifying server name and database name will be enough. Otherwise point to database file.

Now do that same for DB2 as well. Then follow the steps outlined in my previous email.

One thing that you missed was the DSNs.

Works very well for me here.

You will need these DSN names within Remote Server wizard too. Once you create these proxy tables.

All you need to do is just select * from table.

Greetings from Canada!!!
If you need further help do not hesitate to ask as I have got this to work without any problems.
PicklesAuthor Commented:
The DSN already exists. In this case, it's real name is MELBOURNE, and I have a different login / password, but by using the 'Test Connection' button on the first tab, I can confirm the connection works.

When you say 'follow the steps in the previous email' - which steps do you mean? using CREATE SERVER, or via the Sybase Central interface?

I am getting the DSN not found when trying to CREATE EXISTING TABLE - even though the DSN is recognised in the CREATE SERVER statement.

From your steps for Sybase Central, I'm getting a 'stored procedure not found' error.

Either way, the DSN exists and I can connect to the database, so I don't think it's the ODBC settings that are the problem.

>When you say 'follow the steps in the previous email' - which steps do you mean? using >CREATE SERVER, or via the Sybase Central interface?

Via Sybase Central interface. That is the easiest way.
Would you check to see if 'sp_remote_tables'  exist in your database? Internally this is the proc wizard uses to create a server. Is this a migrated database from version 5? I wonder when Sybase added this funtionality.

I am able to see that proc ('sp_remote_tables' ) as a system proc from PowerBuilder SQL DB connection.

Under Table folder, there is icon named 'Add Proxy Table'. Would you try to add the table using that wizard.

Execute this command from ISQL and see what error messge you get,
sp_remote_tables  MELBOURNE

If this proc does not exist you should the 'not found' error otherwise a different error message should popup.

Install cd may come with a sql file that you can install to get this proc back, i think. If not unloading and reloading the database may add these system procs back into the database.

Also see this link. It seems there is an EBF for sp_remote_tables related issues.

Let me know how it goes. I will help you further to get this going.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PicklesAuthor Commented:
The release notes for ASA 6.0.1 ( contain a correction regarding sp_remote_tables which may indicate that the procedure came in with version 6.

It seems the procedure is not on our database.

I believe that we did upgrade from version 5, but I need to confirm this with the DBA - so at this stage I will see what they want to do with trying to add the procedure from the CDs... I will keep you posted.

Thanks again,


Would you need further help? If not, please close this issue. :)

PicklesAuthor Commented:
Just a postscript -

I left this at the point where it was up to the DBA to decide whether we should somehow get sp_remote_tables re-installed. As such, we have not yet implemented this solution, but I have accepted the answer, because it seems almost certain to do the job.

(The workaround we used was to perform the select on one database, then import the results to a temporary table in the second database - not desirable because data may be modified in the time it takes to carry out the procedure, but adequate for what we needed to do.)

Thanks for the help!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.