Distributing Queries
-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 7 - Distributed Queries
--
-- Paul Nielsen
-----------------------------------------------------------
-------------------------------------------------
-- Linking to External Data Sources
EXEC sp_addlinkedserver
@server = 'HPPRESENT\SECOND',
@srvproduct = 'SQL Server';
EXEC sp_addlinkedserver
@server = 'nyack',
@datasrc = 'HPPRESENT\SECOND',
@srvproduct = '',
@provider='SQLOLEDB';
-- Viewing Linked Servers
SELECT [Name], Product, Provider, Data_source
FROM sys.servers
WHERE Is_Linked = 1;
-- Drop a linked server
EXEC sp_DropServer @server = 'Nyack';
-- Distributed Security and Logins
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'HPPRESENT\SECOND',
@useself = 'false',
@locallogin = 'HPPRESENT\total',
@rmtuser = 'sa',
@rmtpassword = 'P@s$w0rD';
-- Linking with Excel
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:\SQLData\CHA1_Schedule.xls',
@provstr = 'Excel 5.0'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'CHA1_Schedule',
@useself = 'false';
-- Linking with Microsoft Access
EXEC sp_DropServer @server = 'CHA1_Customers';
EXEC sp_addlinkedserver
'CHA1_Customers',
'Access 2003',
'Microsoft.Jet.OLEDB.4.0',
'C:\SQLData\CHA1_Customers.mdb';
----------------------------------------
-- Executing Distributed Queries
-- Accessing a Local SQL Server Database
USE CHA2;
SELECT LastName, FirstName
FROM OBXKites.dbo.Contact;
SELECT LastName, FirstName
FROM OBXKites..Contact;
-- Four Part Name
-- to SQL Server
SELECT LastName, FirstName
FROM [HPPresent\Second].Family.dbo.person;
-- to Excel
SELECT DISTINCT [Base Camp]
FROM CHA1_Schedule...[Base_Camp]
WHERE [Base Camp] IS NOT NULL;
-- to Access
SELECT *
FROM CHA1_Customers...Customers
UPDATE CHA1_Customers...Customers
SET ContactLastName = 'Wilson'
WHERE CustomerID = 1;
-- Four Part Name Black Box
Select o.orderid, c.contactcode, o.ordernumber, quantity
from [HPPresent\Second].obxkites.dbo.orderdetail od
join [HPPresent\Second].obxkites.dbo.[order] o
on o.orderid = od.orderid
join [HPPresent\Second].obxkites.dbo.contact c
on o.contactid = c.contactid
where
-- comment out combinations of where clause conditions
c.contactcode = '102'
and o.ordernumber = 3
and o.Orderid = '289623A7-09B8-4E9B-A2A3-075F9E6C34AC';
-- without linked server - SQL Server
SELECT FirstName, Gender
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=HPPresent\Second;Trusted_Connection=yes;'
).Family.dbo.Person;
-- without linked server - Access
SELECT ContactFirstName, ContactLastName
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source = C:\SQLData\CHA1_Customers.mdb')...Customers;
-----------------------------------------------
-- Using Pass-Through Queries
--OpenQuery()
SELECT *
FROM OPENQUERY(CHA1_Schedule,
'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"');
UPDATE -- to Excel
OPENQUERY(CHA1_Schedule, 'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"')
SET [Base Camp] = 'Ashville'
WHERE Tour = 'Gauley River Rafting';
-- OpenRowSet
SELECT *
FROM OPENROWSET ('SQLNCLI', 'Server=hppresent\second;Trusted_Connection=yes;',
'SELECT LastName, FirstName
FROM Family.dbo.person;')
-------------------------------------------------------
-- Controlling Distributed Transactions
USE Family;
SET xact_abort on;
BEGIN DISTRIBUTED TRANSACTION;
UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 10;
UPDATE [hppresent\second].Family.dbo.Person
SET LastName = 'Johnson'
WHERE PersonID = 10;
COMMIT;
0 Response to "Distributing Queries"
Post a Comment