Changing DBs in one connection

[Laphan]

Hi All

I've done an ASP which connects to an SQL DB.

My problem is that I want to distribute this ASP page, but I can't do a
straight DSN=MyDSN to point to the DSN, because I need to play with the
Master DB first (to get a list of dbs) and then when this db choice is
posted I use the actual selected DB. At present, this means I have to
specify the whole DSN(less) string (eg, server name, username, password,
database) so that I can use master and then the db.

Is there a way of specifying a straight DSN, which I know that they will
have set up and then change the DB as I do my SQL-ie bit?

Many thanks

Rgds

Laphan

[Aaron Bertrand - MVP]

Why can't you run a query like this:

set rs = conn.execute("SELECT * FROM database1.dbo.table_name")
....
set rs = conn.execute("SELECT * FROM database2.dbo.table_name")

Your connection string does not need to be restricted to a specific DB. You
can use three-part naming to access any database, as long as the user has
access to the specified database.

BTW, for a list of DBs, you can call this from any database, without having
to connect directly to master:

SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA

Also, don't use a DSN!!!! [url]http://www.aspfaq.com/2126[/url]






"Laphan" <news@FrozenMoles.co.uk> wrote in message
news:bjiknk$7pm$8@hercules.btinternet.com...

> Hi All
>
> I've done an ASP which connects to an SQL DB.
>
> My problem is that I want to distribute this ASP page, but I can't do a
> straight DSN=MyDSN to point to the DSN, because I need to play with the
> Master DB first (to get a list of dbs) and then when this db choice is
> posted I use the actual selected DB. At present, this means I have to
> specify the whole DSN(less) string (eg, server name, username, password,
> database) so that I can use master and then the db.
>
> Is there a way of specifying a straight DSN, which I know that they will
> have set up and then change the DB as I do my SQL-ie bit?
>
> Many thanks
>
> Rgds
>
> Laphan
>
>

[Manohar Kamath [MVP]]

You can issue a USE statement within a connection context and then start
issuing your queries:

USE myDB1;
-- This goes against myTable in myDB1
SELECT * FROM myTable;

USE myDB2;
-- This goes against myTable in myDB2
SELECT * FROM myTable;


--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Laphan" <news@FrozenMoles.co.uk> wrote in message
news:bjiknk$7pm$8@hercules.btinternet.com...

> Hi All
>
> I've done an ASP which connects to an SQL DB.
>
> My problem is that I want to distribute this ASP page, but I can't do a
> straight DSN=MyDSN to point to the DSN, because I need to play with the
> Master DB first (to get a list of dbs) and then when this db choice is
> posted I use the actual selected DB. At present, this means I have to
> specify the whole DSN(less) string (eg, server name, username, password,
> database) so that I can use master and then the db.
>
> Is there a way of specifying a straight DSN, which I know that they will
> have set up and then change the DB as I do my SQL-ie bit?
>
> Many thanks
>
> Rgds
>
> Laphan
>
>