retrieving data from different servers

[llafretaw]

Hi,
I'm trying to retrieve data where there is a match from two different db which are on different servers. Basically server no.1 where I have to run the code from, only has the object's code, but server no.2 has it name and code. Basically I want to create a join which returns the code and name, only where the code that resides in server no.1 resides on server no.2 as well. I have tried different variations of the below code, but to no joy, but I'm not even quite sure if its possible so I may be trying in vain. anyway the join I was trying to do and amending is as follows:

SELECT s1.object_code, s2.object_desc
from server1.dbname.table s1, server2.dbname.table s2
where s1.object_code = s2.object_code

any help would b appreciated.

 

[Tim1]

Hiya,

You cannot run across multiple servers on Sybase (not up to version 12 anyway).

All you can do is get the DBA to create a proxy table, which is basically a behind the scenes link between the two servers, so that the table appears to be on the server that you are running your code from, where as, actually, it resides on the other server.

HTH

Tim

 

[Guest_imported]

cheers 4 the info

 

[babeo]

Hi Tim1,

I really want to create a proxy table too for connection between 2 different servers. If you know how to, could you show me? Thanks

 

[jhonty]

Hi,

Another simpler way is accessing thru the remote server configuration, u need to add the server1 into server2 by using sp_addserver command (see the syntax) add in its interface file, give proper access to any of the user then write this query within one small proc in server2 by calling another remote proc(this proc will be in server1) "exec srv.db.user.proc @code output" within it, and use its o/p for this query, if it exist in server1. Just check the manual for using remote proc. .

rgds,