access two databases within one database server

[Former Member]

I want to access one database from another, but they both are on the same database server. So when I try do it by Proxy tables I can't create remote server: "[Sybase][ODBC Driver][SQL Anywhere]Unable to connect, server definition is circular". Please help.

[Breck_Carter · Accepted solution]

Here's an sample that works...

"%SQLANY12%\\Bin32\\dbinit.exe" ddd1.db
"%SQLANY12%\\Bin32\\dbinit.exe" ddd2.db

"%SQLANY12%\\Bin32\\dbspawn.exe" -f "%SQLANY12%\\Bin32\\dbeng12.exe" -n ddd ddd1.db ddd2.db

"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd;DBN=ddd1;UID=dba;PWD=sql"
"%SQLANY12%\\Bin32\\dbisql.com" -c "ENG=ddd;DBN=ddd2;UID=dba;PWD=sql"

---------------------------------------------------------------------
-- On ddd2

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 2 );
INSERT t VALUES ( 2, 2 );
COMMIT;

---------------------------------------------------------------------
-- On ddd1

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd;DBN=ddd2';

CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere
   TO ddd2_server 
   REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

CREATE EXISTING TABLE proxy_t 
   AT 'ddd2_server...t';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

pkey,data
1,2
2,2

[Former Member · Accepted solution]

Thanks! That worked ;D