Example 13-1
------------------------------------------------------------------------------------------------
USE master
GO
EXEC sp_addlinkedserver 
@server='ArtDatabase', 
@srvproduct='',
@provider='SQLOLEDB', 
@datasrc='NAM',
@catalog='Art'

Example 13-2
------------------------------------------------------------------------------------------------
USE master
GO
EXEC  sp_addlinkedsrvlogin 
	@rmtsrvname ='ArtDatabase',
	@useself  =false,
	@rmtuser ='sa',
	@rmtpassword =''


Example 13-3
------------------------------------------------------------------------------------------------
USE master
GO
EXEC sp_droplinkedsrvlogin 
@rmtsrvname =  'ArtDatabase' , 
@locallogin =  'sa'



Example 13-4
------------------------------------------------------------------------------------------------

USE master
GO
EXEC sp_dropremotelogin 
@remoteserver =  'ArtDatabase' , 
@loginame ='sa',
@remotename ='sa'


Example 13-5
------------------------------------------------------------------------------------------------

sp_dropserver 'ArtDatabase', 'droplogins'


Example 13-6
------------------------------------------------------------------------------------------------
USE master
GO
EXEC sp_addlinkedserver 
   @server = 'EMAIL', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\mails.mdb'
GO

Example 13-7
------------------------------------------------------------------------------------------------
USE master
GO
EXEC  sp_addlinkedsrvlogin 
	@rmtsrvname ='Test',
	@useself  =false,
	@rmtuser ='Admin',
	@rmtpassword =''



Example 13-8
------------------------------------------------------------------------------------------------
USE master
GO
EXEC sp_droplinkedsrvlogin 
@rmtsrvname =  'Test' , 
@locallogin =  'Admin'

Example 13-9
------------------------------------------------------------------------------------------------
USE master
GO
EXEC sp_dropserver 
'Test' , droplogins


Example 13-10
------------------------------------------------------------------------------------------------
select * from 
OPENQUERY ( CALENDAR , 'select * from tblGroup')

Example 13-11
------------------------------------------------------------------------------------------------
select GrpNo ,  GrpName  from 
OPENQUERY ( CALENDAR , 'select * from tblGroup')


Example 13-12
------------------------------------------------------------------------------------------------
select * from 
OPENQUERY ( ACCESS , 
		'select Top 5 CompanyName,
		Sum(Quantity) AS TotalQuantity, 
		Sum(Quantity*UnitPrice) As Amount 
		From Customers C, Orders O, [Order Details] D 
		Where C.CustomerID=O.CustomerID 
		And O.OrderID=D.OrderID 
		Group by CompanyName')


Example 13-13
------------------------------------------------------------------------------------------------
select top 3 * from 
OPENQUERY ( ACCESS , 
		'select Top 5 CompanyName,
		Sum(Quantity) AS TotalQuantity, 
		Sum(Quantity*UnitPrice) As Amount 
		From Customers C, Orders O, [Order Details] D 
		Where C.CustomerID=O.CustomerID 
		And O.OrderID=D.OrderID 
		Group by CompanyName')


Example 13-14
------------------------------------------------------------------------------------------------
select * from 
OPENQUERY ( EXCEL , 
		'select Name, Email From MyData')


Example 13-15
------------------------------------------------------------------------------------------------
SELECT   CategoryID, CategoryName
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=NAM;User ID=sa;Password='
         ).Northwind.dbo.Categories

Example 13-16
------------------------------------------------------------------------------------------------
SELECT   CategoryID, CategoryName
FROM      OPENDATASOURCE(
         'Microsoft.Jet.OLEDB.4.0',
         'Data Source=C:\Northwind.mdb;User ID=Admin;Password='
         )...Categories


Example 13-17
------------------------------------------------------------------------------------------------
SELECT Name,Email 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;
Extended properties=Excel 5.0')...Sheet1$

Example 13-18
------------------------------------------------------------------------------------------------
USE Northwind
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','NAM';'sa';'',
   'SELECT * FROM Northwind.dbo.Customers ORDER BY companyname') AS a
GO


Example 13-19
------------------------------------------------------------------------------------------------

USE Northwind
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=NAM;UID=sa;PWD=',
   Northwind.dbo.Customers) AS a
ORDER BY a.CompanyName
GO


Example 13-20
------------------------------------------------------------------------------------------------
USE Northwind
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\northwind.mdb';'admin';'', Customers) AS a
ORDER BY a.CompanyName
GO


Example 13-21
------------------------------------------------------------------------------------------------

USE Northwind
GO
SELECT c.*, o.*
FROM Customers AS c INNER JOIN 
   OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\northwind.mdb';'admin';'', Orders) 
   AS o
   ON c.CustomerID = o.CustomerID 
GO


Example 13-22
------------------------------------------------------------------------------------------------


USE Northwind
GO
SELECT CustomerID,CompanyName
FROM Customers 
Union
SELECT Cast(SupplierID as varchar(10)),CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\northwind.mdb';'admin';'', Suppliers) 
   AS S
  
GO

Example 13-23
------------------------------------------------------------------------------------------------

SELECT 'SQLServer' as TYPE,CustomerID,CompanyName
FROM Customers 
Union
select 'ACCESS' as TYPE,CustomerID,CompanyName from 
OPENQUERY ( ACCESS , 
		'select CustomerID,CompanyName
		From Customers ')

