The object name <TableName> contains more than the maximum number of prefixes. The maximum is 2

Question

I am trying to pull records from two tables in different servers using single query. I am using the below format in select query .... but all i get is the above error.

is there a way i can pull records from two servers in single query like

select * from server1.db1.schema1.table1 s1,server2.db2.schema2.table2 s2 where s1.col1 = s2.col2

i am using sybase and rapidsql.

EDIT: i am using Sybase Adaptive server enterprise 15.5 and rapid SQL 8.1.0

Answer 1

The reason you are getting a syntax error, is that Sybase ASE can not reference external databases and tables that way for queries. Queries only support the syntax DATABASE.OWNER.TABLE or DATABASE..TABLE.

With Sybase ASE you can query across multiple servers using Component Integration Services (CIS). This allows you to setup proxy tables or proxy databases that pull data that resides on a remote server. The remote server needs to be added to the local server using sp_addserver, and should either be in the interfaces/sql.ini or accessible via LDAP.

From there you have to add the login information for the remote server using sp_addexternalogin

Next you will have to connect to the remote server, and define your proxy tables or proxy databases.

Once you have completed that, you can treat the proxy tables/databases as local to your system for the purpose of queries, as long as the local servers is connected to the remote server.

I highly suggest checking out the CIS documentation linked above, as there are some tutorials on setting it up.