Same question again - tables fro 2DBs

  • Thread starter Thread starter Tina
  • Start date Start date
T

Tina

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

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
 
Back
Top