2 DBs Ref by 1 DataAdapter?

[Tina]

I have two SQL Server databases and I want to reference tables from both DBs
in the SQL. How can I have a connection object that points to both DBs?
Thanks,
T

 

[Jim Hughes]

Assuming that they are on the same server, use the three part naming
convention

databasename.owner.tablename

e.g.

select * from northwind.dbo.customers
select * from pubs.dbo.authors

 

[Chris Taylor]

Hi Tina,

If the database servers are on separate machines you can configure a linked
server or use ad hoc distributed queries.. This can be done from within the
Enterprise Manager or you can use the sp_addlinkedserver procedure, for
ad-hoc distributed queries take a look at OPENROWSET.

In both cases you will be setting up a single connection to one server and
the server will manage the connection and retrieval of data from the other
server. For databases on a single server you merely need to prefix the
object name with the database name to query accross databases.

Hope this helps

 

[Tina]

When using the data adapter wizard, a specific database connection must be
chosen ie. either northwind or pubs and then only tables from that
connection are available in the querybuilder.

I guess my question was "how can this be done using the DataAdapter wizard
and querybuilder.
T