Consultas Distribuidas - Primera Parte

-- Agregar un Servidor Externo
sp_addlinkedserver
@Server = 'PORTATIL',
@srvproduct='SQL Server'

-- Ver Lista de Servidores
SELECT * FROM sys.servers s

-- Borrar un Servidor
EXEC sp_dropserver @server = 'PORTATIL'

-- Agregar Logins y Seguridad
sp_addlinkedsrvlogin
@rmtsrvname = 'PORTATIL',
@useself = 'False',
@locallogin = 'BERNARDO\Bernardo',
@rmtuser = 'sa',
@rmtpassword = '123'

-- Agregar Servidor de Excel
EXEC sp_addlinkedserver
@Server = 'Phones',
@SrvProduct = 'Excel',
@Provider='Microsoft.Jet.OleDB.4.0',
@DataSrc = 'c:\Data\Phones.xls',
@ProvStr='Excel 5.0'


EXEC sp_addlinkedsrvlogin
@rmtSrvName= 'Phones',
@UseSelf= 'False'


-- Agregar Servidor de Access
EXEC sp_addlinkedserver
'Telef',
'Access 2003',
'Microsoft.Jet.OLEDB.4.0',
'c:\Data\Phones.mdb'


sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE


-- Accediendo a Datos Locales
SELECT * FROM Northwind.dbo.Customers c
SELECT * FROM Northwind..Customers c


-- Cuatro Partes de Nombre
SELECT * FROM PORTATIL.Northwind.dbo.Customers

SELECT * FROM phones...Telefonos


-- Sin linked server - SQL Server
SELECT *
FROM OPENDATASOURCE(
'SQLNCLI',
'Data Source=PORTATIL;User ID=sa;Pwd=123;'
).Northwind.dbo.Customers;

-- Sin linked server - Access
SELECT ContactFirstName, ContactLastName
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source = C:\SQLData\CHA1_Customers.mdb')...Customers;


-- OpenRowSet
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=PORTATIL;Uid=sa;Pwd=123;','SELECT * FROM Northwind.dbo.Customers;') AS a