Solved

select from two databases

Posted on 2003-11-10
18
2,899 Views
Last Modified: 2012-05-04
I want to do this:

select
  other_table_count = (select count(odt.id)
                                  from other_db_table odt
                                  where odt.id = tdt.id)
from
  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.

Thanks,

Chris.
0
Comment
Question by:Pickles
  • 9
  • 6
  • 2
  • +1
18 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 9720113
select tdt.id count(*)
from this_db.table_name tdt, other_db.table_name odt
where tdt.id = odt.id
group by tdt.id
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9720737
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
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 9720743
yes chrisking - you are correct
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9722105
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)
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9722168
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

CREATE SERVER local_db2
CLASS 'asaodbc'
USING 'testasa_db2'
CREATE SERVER local_db3
CLASS 'asaodbc'
USING    'testasa_db3'
or using JDBC:

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

CREATE EXISTING TABLE employee
AT 'local_db2...employee'
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9722685
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.

HTH
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9725156
Pickles,

Did that work?
0
 

Author Comment

by:Pickles
ID: 9726093
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.
0
 

Author Comment

by:Pickles
ID: 9726825
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!

Thanks,

Chris.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Pickles
ID: 9726836
PS. The DB Engine is dbsvr7.exe.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9726925
Pickles,

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!!!
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9726932
If you need further help do not hesitate to ask as I have got this to work without any problems.
0
 

Author Comment

by:Pickles
ID: 9727054
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.

Chris.
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 250 total points
ID: 9727310
>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.
http://www.sybase.com/detail?id=1009485


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

Expert Comment

by:namasi_navaretnam
ID: 9727355
0
 

Author Comment

by:Pickles
ID: 9727804
The release notes for ASA 6.0.1 (http://www.sybase.com/detail?id=47915) 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,

Chris.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9982646
Chris,

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

Regards.
0
 

Author Comment

by:Pickles
ID: 9982730
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!

Chris.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now