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.

asked 31 May '11, 12:32

henginy's gravatar image

henginy
406151827
accept rate: 0%

edited 01 Jun '11, 05:02

Try again... it does work (see my edited replay).

(01 Jun '11, 07:02) Breck Carter

I cannot check it atm but I'll return to this again.

(03 Jun '11, 08:07) henginy

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.

permanent link

answered 31 May '11, 12:53

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

edited 31 May '11, 12:54

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

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 :)

permanent link

answered 31 May '11, 15:18

Breck%20Carter's gravatar image

Breck Carter
26.9k440613886
accept rate: 21%

edited 01 Jun '11, 07:02

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

(01 Jun '11, 05:00) henginy
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×101
×90

question asked: 31 May '11, 12:32

question was seen: 1,501 times

last updated: 03 Jun '11, 08:07