sp_configure 'show advanced options', 1
go
reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
--0
USE [master]
GO
CREATE LOGIN [ZALICZENIE] WITH PASSWORD=N'12345', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [northwind]
GO
CREATE USER [ZALICZENIE] FOR LOGIN [ZALICZENIE]
GO
GRANT INSERT ON [dbo].[Products] TO [ZALICZENIE]
GO
GRANT SELECT ON [dbo].[Products] TO [ZALICZENIE]
GO
GRANT INSERT ON [dbo].[Customers] TO [ZALICZENIE]
GO
GRANT SELECT ON [dbo].[Customers] TO [ZALICZENIE]

--1
use [master]

exec sp_addlinkedserver @server='Access',
@srvproduct='Access',
@provider='Microsoft.ACE.OLEDB.12.0',
@datasrc='C:\Folder\Northwind.mdb'
go
sp_addlinkedsrvlogin @rmtsrvname = 'Access'
     ,@useself = 'FALSE'
     ,@locallogin = 'ZALICZENIE'
     ,@rmtuser = 'Admin'
     ,@rmtpassword = ''
go

--
sp_addlinkedserver @server= 'OrclDB'
	 ,@srvproduct= 'Oracle'
	 ,@provider= 'OraOLEDB.Oracle'
	 ,@datasrc= 'hw12'

 sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB'
     ,@useself = 'FALSE'
     ,@locallogin = 'ZALICZENIE'
     ,@rmtuser = 'pd202318'
     ,@rmtpassword = '12345'

 sp_linkedservers

--2
create procedure KLPR
 (
 @Klient nvarchar(50)
 )
 as ( SELECT count (p.CustomerID)
From OpenRowset('Microsoft.ACE.OLEDB.12.0',
'C:\Folder\Northwind.mdb';'admin';'',
'SELECT IDKlienta as CustomerID  from Zamówienia') as p
where p.CustomerID like @Klient )

--
SELECT * FROM OPENQUERY (OrclDB, 'SELECT count (CustomerID) from Orders WHERE CustomerID = '' VINET''');

--3 Dostawca Produkt Wartość

create procedure JD
 (
 @Dostawca nvarchar(50)
 )
 as (select sum(o.Quantity * o.UnitPrice), s.CompanyName
from northwind.dbo.[Order Details] as o inner join
	  northwind.dbo.Products AS p
	  on o.ProductID = p.ProductID inner join
OPENROWSET('OraOLEDB.Oracle', 'hw12';'HR';'hr',
     'SELECT xxx.CompanyName, xxx.SupplierID
      FROM Northwind.dbo.Suppliers xxx') AS s on p.SupplierID = s.SupplierID
where s.CompanyName like @Dostawca)

--5 Jaki
--Tab klient
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\Folder\Northwind.mdb';'admin';'',
     'SELECT c.Nazwafirmy as CompanyName, c.IDklienta as CustomerID
      FROM Northwind.dbo.Customers c')  c

--Tab Zam
Select *
FROM OPENROWSET('SQLOLEDB', 'IBM-02';'sa';'praktyka',
     'SELECT o.orderid, o.orderdate, o.customerid, o.employeeid
      FROM Northwind.dbo.Orders o')  o

--Tab Praco
Select *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\Folder\Northwind.mdb';'admin';'',
     'SELECT o.IDpracownika as employeeid, o.IDklienta as customerid
      FROM Northwind.dbo.Employees o')  e
	 select * from customers

	  SELECT c.CustomerID, o.OrderID, e.EmployeeID
	  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\Folder\Northwind.mdb';'admin';'',
     'SELECT xx.Nazwafirmy as CompanyName, xx.IDklienta as CustomerID
      FROM Northwind.dbo.Customers xx')  c
	  inner join
      Northwind.dbo.Orders as o on c.CustomerID = o.CustomerID
	  inner join OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\Folder\Northwind.mdb';'admin';'',
     'SELECT xxx.IDpracownika as employeeid, xxx.IDklienta as customerid
      FROM Northwind.dbo.Employees xxx')  e on o.EmployeeID=e.EmployeeID

--z jakiej kategorii (serwer lokalny) mamy jakie produkty (serwer ibm-06)
--ktore dostarczone zostaly przez jakiego dostawce (serwer ibm-02)
select c.CategoryName, p.ProductName, p.UnitPrice, s.CompanyName
from northwind.dbo.Categories as c inner join
OPENROWSET('SQLNCLI', 'IBM-06';'sa';'praktyka',
     'SELECT xxx.productname, xxx.unitprice , xxx.CategoryID  , xxx.SupplierID
      FROM Northwind.dbo.Products xxx') AS p
	  on c.CategoryID = p.CategoryID inner join
OPENROWSET('SQLNCLI', 'IBM-02';'sa';'praktyka',
     'SELECT xxx.CompanyName, xxx.SupplierID
      FROM Northwind.dbo.Suppliers xxx') AS s on p.SupplierID = s.SupplierID;

--wczytywanie z excela(xls)
SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=NO;Database=C:\Folder\listy.xls',
    'select * from [oceny_do_www$]')

--zalozyc tabele w sql serverze na podstawie zapytania do oracle
-- jakie produkty mialy jaka wartosc sumarczynej sprzedazy w latach 98, 97
select a.productname,
a.suma,
convert(datetime, a.MIESIAC_ROK+'-01T00:00:00.000', 126) MMM
 FROM OPENROWSET('OraOLEDB.Oracle', 'hw12';'HR';'hr',
     'select p.PRODUCTNAME, sum(od.UNITPRICE*od.QUANTITY) SUMA, to_char(o.ORDERDATE, ''YYYY-MM'') MIESIAC_ROK from NORTHWIND.PRODUCTS p inner join NORTHWIND.ORDERDETAILS od on
p.PRODUCTID=od.PRODUCTID inner join NORTHWIND.ORDERS o on
od.ORDERID=o.ORDERID

group by p.PRODUCTNAME, to_char(o.ORDERDATE, ''YYYY-MM'')') as a;

--
sp_addlinkedserver @server= 'IBM01'
 ,@srvproduct= 'SQLServer'
 ,@provider= 'SQLNCLI11'
 ,@datasrc= 'IBM-01'
go
sp_configure "user options", 512
go
reconfigure

go
sp_addlinkedsrvlogin @rmtsrvname = 'IBM01'
     ,@useself = 'FALSE'
     ,@locallogin = 'sa'
     ,@rmtuser = 'renatera'
     ,@rmtpassword = '12345'