Query Multiple Databases within SQL Server

[stapet]

I am a newbie at programming with SQL Server 2000. I am wondering, is it possible to Query more than one database at a time within SQL Server? Any help would be greatly appreciated.
The whole purpose: I would like to report off of two different databases without having the tables stored in both places.
Thanks in advance to anyone that can help!

 

[Zirak]

You can create a view from the other database and query the view.

 

[jazerr]

You can connect a second, third, fourth...nth...database by using dbname..tablename..columname

 

[stapet]

I have tried to connect to a second database, but keep getting an error message. Am I doing this correctly?

Use database1
Select f_name
From Table1
Where f_name IN
(Select database2.table2.fname)

Thank again for the help!

 

[SQLBill]

When you use the 'fully qualified' naming convention, you either need the database owner or double dots between the database name and the table.


Syntax: server.database.owner.table
or
server.database..table

If the server is the same, you don't need to include it.

-SQLBill

 

[Corinne]

Can this be done in a stored procedure?