|
Is there an ASA equivalent for MSSQL "Use" keyword that enables selecting a database when connected to multiple servers? Update:
Yes, I wanted to be able to query all servers from a single Interactive SQL window. Now I understand these windows are coupled with a specific connection. Actually one could tell from the "Tools->Lookup Table Name" menu which lists only the current database's tables, but I thought it might be possible somehow. Update 2: I tried |
|
There is no such concept when using SQL Anywhere. For ASE compatibility, you can actually execute the "USE" statement when connected to SA, but the statement is basically ignored. In both ASE and SQL Server, you "connect" to the master database and then switch databases as desired; but in SA, you connect to a specific SQL Anywhere database and cannot simply switch databases without creating an entirely new connection to the new database. BTW, I am not sure what you mean by "when connected to multiple servers". Are you in fact referring to the use of the remote data access feature to access multiple servers/databases from the same SQL Anywhere connection. If so, then you access different databases via the use of proxy tables which map to a specific table within a database when used in a query. If my answer is entirely off the mark, then please provide some additional details to clarify what exactly you are asking about. 1
Your answer is right on the track, thank you. Is there any plan to add this feature?
(01 Jun '11, 04:58)
henginy
Replies hidden
I am glad I could help. There are no plans at the moment to add such a feature to SQL Anywhere.
(01 Jun '11, 07:10)
Karim Khamis
|
|
Since July 2009 it's been my intention to write an article "The USE Statement In SQL Anywhere" but every time I try, I run into some bug/limitation/annoyance. However, depending on your needs, perhaps you can make the following work for you... Here's a demonstration of CONNECT with Version 9... -- Run these commands to set up a test environment...
"%ASANY9%\win32\dbinit.exe" ddd1.db
"%ASANY9%\win32\dbinit.exe" ddd2.db
"%ASANY9%\win32\dbinit.exe" ddd3.db
"%ASANY9%\win32\dbspawn.exe" -f "%ASANY9%\win32\dbeng9.exe" -o dbeng12_log_ddd1.txt -oe dbsrv12_log_fatal_ddd1.txt -os 10M -x none -zl -zp -zt ddd1.db
"%ASANY9%\win32\dbspawn.exe" -f "%ASANY9%\win32\dbeng9.exe" -o dbeng12_log_ddd2.txt -oe dbsrv12_log_fatal_ddd2.txt -os 10M -x none -zl -zp -zt ddd2.db
"%ASANY9%\win32\dbspawn.exe" -f "%ASANY9%\win32\dbeng9.exe" -o dbeng12_log_ddd3.txt -oe dbsrv12_log_fatal_ddd3.txt -os 10M -x none -zl -zp -zt ddd3.db
"%ASANY9%\win32\dbisql.exe"
-- Clear the dbisql "can't connect" dialog box, and proceed thusly...
CONNECT USING 'eng=ddd1;dbn=ddd1;uid=dba;pwd=sql;';
SELECT STRING ( 'Database ', DB_PROPERTY ( 'Name' ),
' on ', @@VERSION,
' server ', PROPERTY ( 'Name' ) ) AS "Connected to...";
/*
This appears in the Messages pane...
Connected to "ddd1" on "ddd1".
This appears in the Results pane...
Connected to...
'Database ddd1 on 9.0.2.3924 server ddd1'
*/
CONNECT USING 'eng=ddd2;dbn=ddd2;uid=dba;pwd=sql;';
SELECT STRING ( 'Database ', DB_PROPERTY ( 'Name' ),
' on ', @@VERSION,
' server ', PROPERTY ( 'Name' ) ) AS "Connected to...";
/*
This appears in the Messages pane...
Connected to "ddd2" on "ddd2".
Connected to...
'Database ddd2 on 9.0.2.3924 server ddd2'
*/
CONNECT USING 'eng=ddd3;dbn=ddd3;uid=dba;pwd=sql;';
SELECT STRING ( 'Database ', DB_PROPERTY ( 'Name' ),
' on ', @@VERSION,
' server ', PROPERTY ( 'Name' ) ) AS "Connected to...";
/*
This appears in the Messages pane...
Connected to "ddd3" on "ddd3".
Connected to...
'Database ddd3 on 9.0.2.3924 server ddd3'
*/
Note that the dbisql window title bar changes each time: "ddd3 (DBA) on ddd3". If memory serves, the problems I had was not being able to maintain multiple connections and switch via SET CONNECTION... perhaps it's time to explore that again :) Thank you for the links. I'm updating my answer to include my test with CONNECT.
(01 Jun '11, 05:00)
henginy
|
Try again... it does work (see my edited replay).
I cannot check it atm but I'll return to this again.