Single query from 2 databases

dinesh_bali
dinesh_bali used Ask the Experts™
on
Hi,

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
Comment
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
Commented:
> 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

Commented:
dba.dbo.database2.table2

servername.databasename.ownername.tablename

dba.database2.dbo.table2

Commented:
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
openquery
opendatasource i mean

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

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