dinesh_bali
asked on
Single query from 2 databases
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are both databases on the same server?
If not, you need to add a linked server.
If not, you need to add a linked server.
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
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
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
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
servername.databasename.ow
dba.database2.dbo.table2