How Access Data from another SQL Server database in a query?

[rvancleef]

Does anyone know how to query data from one database to a another in real time?


For example:

Database 1 has a table called People and a field called PersonID

Database 2 has a table called Employees and a field called EmployeeID

I want to build an SQL Statement something like this:

SELECT DB1.People.PersonID, DB2.Employee.EmployeeID,

WHERE …you get the idea


Using this dot notation (that I have seen in some SQL References), I am getting an error to the effect:

“The column prefix does not match the table name or alias name used in the query”

Any ideas????

 

[fluteplr]

you are missing the owner.

database.owner.table.field

or

database..table.field

note owner is usually dbo

 

[rac2]

The fully qualified name of a table has three parts separated by periods.

database_name.owner.table_name

so you just need to put the owner in there.

If the owner is the database owner, as is often true, then it would be

DB1.dbo.People.PersonID

Use Enterprise Manager to see the name of the table owner.
Or EXEC sp_tables in Query Analyzer.