cancel
Showing results for 
Search instead for 
Did you mean: 

ASA equivalent for MSSQL "Use" keyword

Former Member
0 Kudos
3,741

Is there an ASA equivalent for MSSQL "Use" keyword that enables selecting a database when connected to multiple servers?

Update:

"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."

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 CONNECT USING 'dsn=xxx;uid=xxx;pwd=xxx;enginname=xxx;' on an Interactive SQL window, the statement is executed however the window stays on its initial connection.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

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.

Former Member

Your answer is right on the track, thank you. Is there any plan to add this feature?

Former Member
0 Kudos

I am glad I could help. There are no plans at the moment to add such a feature to SQL Anywhere.

Answers (1)

Answers (1)

Breck_Carter
Participant

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...

CONNECT statement

SET CONNECTION statement

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 🙂

Former Member
0 Kudos

Thank you for the links. I'm updating my answer to include my test with CONNECT.