ch 18 - distributed queries.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter  18 - Distributed Queries

-----------------------------------------------------------
-----------------------------------------------------------

-- Note: the author's development machine is named Noli
-- (for the class 4 rapids of the Nolichucky River in West Virginia)
-- with a second instance of SQL Server named: [Noli\SQL2]

-- Local SQL Server Database
USE CHA2

SELECT LastName, FirstName 
  FROM OBXKites.dbo.Contact

SELECT LastName, FirstName 
  FROM OBXKites..Contact

-- Link to Other SQL Server

EXEC sp_addlinkedserver 
  @server = 'Noli\SQL2', 
  @srvproduct = 'SQL Server'

EXEC sp_addlinkedserver 
  @server = 'Yonder', 
  @datasrc = '[Noli\SQL2]',
  @srvproduct = '',
  @provider='SQLOLEDB'

EXEC sp_linkedservers

EXEC sp_DropServer @server = 'Yonder'

-- Distributed Security and Logins

sp_addlinkedsrvlogin 
  @rmtsrvname = 'NOLI\SQL2',
  @useself = 'false', 
  @locallogin = 'NOLI\Paul',
  @rmtuser = 'sa', 
  @rmtpassword = 'secret'  

EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'NOLI\SQL2'

EXEC sp_addlinkedsrvlogin 'NOLI\SQL2', 'false'

EXEC sp_helplinkedsrvlogin

EXEC sp_droplinkedsrvlogin
  @rmtsrvname = 'NOLI\SQL2',
  @locallogin = 'NOLI\Paul' 

EXEC sp_droplinkedsrvlogin 'NOLI\SQL2', NULL

-- Linking with non-SQL Server Data Sources

EXEC sp_droplinkedsrvlogin 'CHA1_Schedule', NULL
EXEC sp_DropServer @server = 'CHA1_Schedule'

EXEC sp_addlinkedserver 
  @server = 'CHA1_Schedule',
  @srvproduct =  'Excel',
  @provider = 'Microsoft.Jet.OLEDB.4.0',
  @datasrc = 'C:\SQLServerBible\CHA1_Schedule.xls', 
  @provstr = 'Excel 5.0'
EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'CHA1_Schedule',
  @useself  = 'false'


EXEC sp_addlinkedserver 
  'CHA1_Customers', 
  'Access 2000', 
  'Microsoft.Jet.OLEDB.4.0', 
  'C:\SQLServerBible\CHA1_Customers.mdb'

----------------------------------------
-- Locally Executed Distributed Queries

-- Four Part Name
SELECT LastName, FirstName 
  FROM [NOLI\SQL2].Family.dbo.person

INSERT BaseCamp(Name)
  SELECT DISTINCT [Base Camp]
    FROM CHA1_Schedule...[Base_Camp]
    WHERE [Base Camp] IS NOT NULL

UPDATE [Noli\SQL2].Family.dbo.Person 
  SET LastName = 'Wilson'
  WHERE PersonID = 1


-- Four Part Name Black Box
Select c.contactcode, o.ordernumber, quantity 
  from [noli\sql2].obxkites.dbo.orderdetail od
    join [noli\sql2].obxkites.dbo.[order] o
      on o.orderid = od.orderid
    join [noli\sql2].obxkites.dbo.contact c
      on o.contactid = c.contactid
  where 
    -- comment out combinations of where clause conditions
    c.contactcode = '102' -- and
  --  o.ordernumber = 1 and
  --  o.Orderid = 'BD0BB9E9-F3BB-452C-8789-C1E5E7D04C17'


-- OpenDataSource()
SELECT FirstName, Gender
  FROM OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=NOLI\SQL2;User ID=Joe;Password=j'
         ).Family.dbo.Person

SELECT ContactFirstName, ContactLastName 
  FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 
   'Data Source = C:\SQLServerBible\CHA1_Customers.mdb')...Customers
 
-- an extra example
SELECT ProductName, UnitsInStock 
  FROM OPENDATASOURCE
    ('Microsoft.Jet.OLEDB.4.0',
      'Data Source = C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb')...Products

UPDATE OpenDataSource( 
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source=C:\SQLServerBible\CHA1_Schedule.xls;
    User ID=Admin;Password=;Extended properties=Excel 5.0'
    )...Tour
  SET [Base Camp] = 'Ashville' 
  WHERE Tour = 'Gauley River Rafting'

SELECT * 
  FROM OpenDataSource( 
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source=C:\SQLServerBible\CHA1_Schedule.xls;
    User ID=Admin;Password=;Extended properties=Excel 5.0'
    )...Tour
  WHERE Tour = 'Gauley River Rafting'

-----------------------------------------------
-- Remote Execution / Pass-Through Distributed Queries

--OpenQuery()

SELECT * 
  FROM OPENQUERY(CHA1_Schedule, 
    'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"')

UPDATE OPENQUERY(CHA1_Schedule,
   'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"')
  SET [Base Camp] = 'Ashville' 
  WHERE Tour = 'Gauley River Rafting'

-- OpenRowSet
SELECT ContactFirstName, ContactLastName 
  FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
  'C:\SQLServerBible\CHA1_Customers.mdb'; 'Admin';'', 
  'SELECT * FROM Customers WHERE CustomerID = 1')
 
UPDATE OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
  'C:\SQLServerBible\CHA1_Customers.mdb'; 'Admin';'', 
  'SELECT * FROM Customers WHERE CustomerID = 1')
  SET ContactLastName = 'Wilson'


-------------------------------------------------------
-- Ditributed Transactions

USE Family
SET xact_abort on
BEGIN DISTRIBUTED TRANSACTION

  UPDATE Person
    SET LastName = 'Johnson'
    WHERE PersonID = 10

  UPDATE [Noli\SQL2].Family.dbo.Person 
    SET LastName = 'Johnson'
    WHERE PersonID = 10

COMMIT