Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 = 'koledzy'
- ,@rmtpassword = '12345'
Add Comment
Please, Sign In to add comment