Single query from 2 databases

dinesh_bali used Ask the Experts™

I am using SQL 2005

If I have 2 database

say database1 and database2

In database1 I have table1 and in database2 I have table 2

can I make query like this

select table1.field1, table1.field2, table2.field1, table2.field2 from dba.dbo.database1.table1 as table1 inner join dba.dbo.database2.table2 as table2

I mean can I make query from 2 databases

It gives me error saying that

Msg 7202, Level 11, State 2, Line 1
Could not find server 'DBA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Can anyone help me

Kind Regards
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
> from dba.dbo.database1.table1
the syntax is servername.databasename.username.tablename.

so, I guess that there is some confusion?
assuming that both databases are on the samer server/instance:
from database1.dbo.table1 table1 inner join database2.dbo.table2 as table2

Top Expert 2006




Are both databases on the same server?

If not, you need to add a linked server.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

ur other server should be linked server
u must first add the server as  a linked server and then run the queries posted aboveif it's a one time job then u could use
opendatasource i mean

         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
to use open query
first add linked server
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',   'MSDAORA',
and then use open query or the querie posted above
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial