Pickles
asked on
select from two databases
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.
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.
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_ta
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_ta
yes chrisking - you are correct
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
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'
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'
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
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
Pickles,
Did that work?
Did that work?
ASKER
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.
Let me work through all the responses and I'll get back to everyone.
ASKER
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.
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.
ASKER
PS. The DB Engine is dbsvr7.exe.
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!!!
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Chris,
Would you need further help? If not, please close this issue. :)
Regards.
Would you need further help? If not, please close this issue. :)
Regards.
ASKER
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.
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.
from this_db.table_name tdt, other_db.table_name odt
where tdt.id = odt.id
group by tdt.id