how to query against two instances of SQL Server in Query Pane

I have registered two SQL Server instances in Management Studio. I can see the tables and databases from both instances.

My problem is how to combine data from both the servers in a single query?

I tries the following

SELECT s.consortium_contact_data_request_id, s.copy_to_exchange_address_book,i.consortium_contact_data_request_id, i.copy_to_exchange_address_book

FROM sandbox.gbiv.dbo.consortium_contact_data_request s,

ivhost04.gbiv.dbo.consortium_contact_data_request i

WHERE (s.copy_to_exchange_address_book IS NOT NULL)

go

but received a message

Msg 7202, Level 11, State 2, Line 1

Could not find server 'sandbox' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.'

Though SANDBOX is registered and is viewable in the Object Explorer.

Thank you,

Piyush Varma

Piyush,

Adding the server in Management Studio's registered servers is not the same thing as creating a linked server. Registered servers is a way for you to organize your servers for easy access. In addition, while Object Explorer can connect to and display multiple servers, the context for a query window is a single server. In order to query two instances (servers) in a single query you need to link the servers. Look in Books Online under Linked Server for more information on establishing a link between two servers.

Cheers,

Dan

|||Thank you, Dan. I was able to add a linked server using following statement:

USE master;
GO
EXEC sp_addlinkedserver
'sandbox',
N'SQL Server'
GO

Then I was able to query from both the servers as below:

SELECT s.consortium_contact_data_request_id, s.copy_to_exchange_address_book,i.consortium_contact_data_request_id, i.copy_to_exchange_address_book
FROM sandbox.gbiv.dbo.consortium_contact_data_request s,
ivhost04.gbiv.dbo.consortium_contact_data_request i
WHERE (s.copy_to_exchange_address_book IS NOT NULL)
go

Thank you,

Piyush