Same question again - tables fro 2DBs

Sorry, but I asked the previous question poorly.

I have two SQL Server databases (on the same server) and I want to reference
tables from both DBs
in the SQL. I know that manually written SQL can reference tables in
multiple databases by prepending the database name when necessary.

However, we use the DataAdapter Configuration Wizard and Querybuilder for
productivity reasons but it seems that this wizard will only allow
connection to a single database withing a single data adapter. Is there a
way to be able to include tables from multiple databases in the Querybuilder
wizard?
Thanks,
T

John
Sun Dec 05 14:46:27 CST 2004

If you know how to manually query across two databases, couldn't you
create a view (or stored procedure) in your primary database that knows
how to get the data from both databases and then use the DataAdapter to
query from the view? Then again you could also create a linked database
configuration on your SQL server. Here's a link on how to accomplish
the second one:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

Hope one of these helps to resolve your issue (though I've never tried
it to find out).

Have A Better One!

John M Deal, MCP
Necessity Software

Tina wrote:
> Sorry, but I asked the previous question poorly.
>
> I have two SQL Server databases (on the same server) and I want to reference
> tables from both DBs
> in the SQL. I know that manually written SQL can reference tables in
> multiple databases by prepending the database name when necessary.
>
> However, we use the DataAdapter Configuration Wizard and Querybuilder for
> productivity reasons but it seems that this wizard will only allow
> connection to a single database withing a single data adapter. Is there a
> way to be able to include tables from multiple databases in the Querybuilder
> wizard?
> Thanks,
> T
>
>

Sahil
Mon Dec 06 00:53:22 CST 2004

Tina,

There is a way, but it isn't very straightforward - you need some database
blackmagic.

You can create a table alias for the linked table in the other database.
Alternatively you could create a view instead.

To tell you the truth - another "hack" is - specify any SQL, and well you
know what the right SQL is supposed to be, so just overwrite the CommandText
with the correct SQL involving two tables - as long as your userid in the
connection string has the proper access rights, it will work.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik




"Tina" <tinamseaburn@removespamexcite.com> wrote in message
news:uaOBs6v2EHA.2180@TK2MSFTNGP10.phx.gbl...
> Sorry, but I asked the previous question poorly.
>
> I have two SQL Server databases (on the same server) and I want to
> reference tables from both DBs
> in the SQL. I know that manually written SQL can reference tables in
> multiple databases by prepending the database name when necessary.
>
> However, we use the DataAdapter Configuration Wizard and Querybuilder for
> productivity reasons but it seems that this wizard will only allow
> connection to a single database withing a single data adapter. Is there a
> way to be able to include tables from multiple databases in the
> Querybuilder wizard?
> Thanks,
> T
>
>