Remote database query

[AppSpecialist]

I need to query a table in a separate sybase database instance (if that is the correct word to use... maybe service is a better word).

This database in on the same server... however cannot be seen via the ISQL session when logged on to the other instance.

If I were to use SQL Server I would setup a linked server and a similiar finctionality exists in Oracle.

Can someone point me in the correct direction?

Thanks

 

[dickiebird]

Try
In an ISQL session :
select * from database1_name..table_name
(You need to have permissions on the table and be a valid user of the other database, I reckon)

HTH Dickie Bird (-)))

 

[AppSpecialist]

This only works for databases listed inside the current Sybase service.

When I log onto one service I use a connection like:
isql -Pxxx -Uyyyy -SCUSTOMER

To logon to the other service to get access to its databases I would use:
isql -Pxxx -Uyyyy -SFINANCIAL

Thanks

 

[dickiebird]

Ok - Dunno then ! Dickie Bird (-)))

 

[sybaseguru]

As Dickie pointed out and if my understanding is correct, from your isql you are using two different sybase servers (please note that a sybase server can have many sybase databases). However in your case you need to contact your DBA to allow you to do remote procedure calls (rpc) from one server to another. Your DBA should do this in both
0) check that both servers have entries in interfaces files
1) sp_addserver <remote_server>, sql_server, <remote_server>
2) sp_addremotelogin <remote_server> , <local_login_name>, <remote_login_name>
tip it helps to keep the remote and local login names and passwords the same!

Once set up you can excecute the following in either server to invoke a procedure in remote servers.
exec remote_server_name.database_name.dbo.<procedure_name>

If you want to see FINANCIAL.<table> in CUSTOMER you need to define proxy tables. Have a look at sybase manuals.

Hope this helps

 

[babeo]

Hi sybaseguru,

I have a question and if you could explain for me.

1) For quick access and view, I use DBArtisan application to connect to all databases (eg A,B,C), however, I can only connect to database A and B, but I can not connect to C.

2) If I am using telnet to connect to A,B,C then I am able to connect to the database of each (including C) using isql.

What is the problem here? Note that I use the same login id in both cases.

Does it mean that I have to add remote server for C or I have to set up proxy for C? if it is so, then how can I make proxy connection?
Thanks

 

[sybaseguru]

Answers)

1) Are databases (A, B anc C) on the same sybase server (when u isql you say -S<SERVER_NAME>.
If they are indeed on different sybase servers (i.e. the isql command you pass has different value in -S<SERVER_NAME>, then isql will work. However, I suspect that either you have not set up the SERVER_NAME in your sql.ini file on Windows for DBArtisan or possibly the Sybase server C is a different version for example Sybase version 12.5. In that case you may be using an old version of DBArtisan which cannot connect to 12.5 sybase server.

please give me the isql in full that you are using for each connection and do 'select @@version' through isql to see which version of sybase the servers on.