Linking another SQL Server instance Part 2 Single query for two server instance

In the part 1 we added one server as linked server to another instance of the mssql server now we will right a sample query which will retrun the result from both the server tables and will give single output

select * from master.sys.objects
union
select * from [inventory].master.sys.objects

it will return the result after combining the result of both the databases of different instance. So you will get the list of objects which are there in both the instances. Now we will take another sample in which we will have the product database in one instance and customer database in another instance we will join them on one field and will get the result after joining them

select productname, productid, customername, customerid from products.dbo.productlist as prolist
	inner join [inventory].customers.dbo.customerlist as cuslist on (cuslist.productid = prolist.productid)

here result from the one instance and customer data is coming from the inventory server

Linking another SQL Server instance Part 1

There can be case or a requirement to attach the another sql server to your existing sql server instance so that you can work on the both server at same time. Get data from both the server from one query as a single result, managing all server from one location or for various work you can utilise it.

Linking another server

for that we have to use sp_addlinkedserver. one of the example

 
EXEC sp_addlinkedserver   
@server='inventory', 
@srvproduct='',
   @provider='SQLNCLI', 
@datasrc='server\mssql2005'

for more information on this visit sp_addlinkedserver (Transact-SQL) – MSDN

Here we added the server\mssql2005 as the inventory server as linked server now we have to add the login information for that we have to use

 
EXEC sp_addlinkedsrvlogin 'inventory', 
'false', 'mydomain\user2', 
'sa', 'sapassword

To get more information on this checkout sp_addlinkedsrvlogin (Transact-SQL) – MSDN

Now that server is linked and ready to be user. we can use the following query to get the data from the server

 
SELECT * FROM [inventory].master.dbo.sysobjects