Do kursu 2781:
http://www.mobilo24.eu/wp-content/uploads/2011/02/Przyklad_Broker_ok.zip
http://www.mobilo24.eu/wp-content/uploads/2013/01/dzien-3.zip
http://www.mobilo24.eu/wp-content/uploads/2013/01/2781-2.zip
http://www.mobilo24.eu/wp-content/uploads/2013/01/skrypt_ns.txt
ENDPOINT
IF EXISTS (SELECT * FROM sys.objects WHERE name = N’tbl_Players’ AND type = 'U’)
DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
–Create the table
CREATE TABLE tbl_Players (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
)
–Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’A. Won’,’India’,10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’A. Cricket’,’India’,10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’B. Dhanman’,’India’,10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’C. Barsat’,’India’,10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’A. Mirza’,’India’,2,3,38, 3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’M. Karol’,’US’,15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’Z. Hamsa’,’US’,3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’K. Loly’,’US’,6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’S. Summer’,’US’,87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’J.June’,’US’,12,510,9, 4988000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’A.Namaki’,’Australia’,1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’Z. Samaki’,’Australia’,2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’MS. Kaki’,’Australia’,40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’S. Boon’,’Australia’,170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’DC. Shane’,’Australia’,28,39,338, 4444499)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’S. Noami’,’Singapore’,165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’Z. Biswas’,’Singapore’,73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’K. Dolly’,’Singapore’,65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’S. Winter’,’Singapore’,7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES(’J.August’,’Singapore’,9,99,98, 890)
If Exists (Select * from sys.objects where name = 'usp_SelectPlayerRecords’ and type = 'P’)
Drop Procedure usp_SelectPlayerRecords
Go
— Create the stored procedure
Create Procedure [dbo].[usp_SelectPlayerRecords]
As
Begin
Select
PlayerID
,PlayerName
, BelongsTo
, MatchPlayed
,RunsMade
,WicketsTaken
,FeePerMatch
From
tbl_Players
End
IF EXISTS ( SELECT NAME FROM sys.http_endpoints WHERE NAME = N’PlayerRecord_EP’ )
DROP ENDPOINT PlayerRecord_EP
GO
CREATE ENDPOINT [PlayerRecord_EP]
STATE=STARTED
AS HTTP
(
PATH=N’/PlayerName’
, PORTS = (CLEAR)
,AUTHENTICATION = (INTEGRATED)
, SITE=N’localhost’
, CLEAR_PORT = 8000
)
FOR SOAP
(
WEBMETHOD 'PlayerList’
( NAME=N'[SSISExperiments].[dbo].[usp_SelectPlayerRecords]’)
, BATCHES=DISABLED
, WSDL=DEFAULT
, DATABASE=N’SSISExperiments’
, NAMESPACE=N’http://SSISExperiments/Players’
)
GO
SZYFROWANIE
CREATE
DATABASE testdb
GO
USE testdb
GO
CREATE
MASTER KEY ENCRYPTION BY PASSWORD=’PassToEncryptDatabaseKey’
GO
CREATE
CERTIFICATE testcert WITH SUBJECT = 'ToEncrypt’
GO
CREATE
TABLE tcrypt (id INT, name VARCHAR (30),
encryptdata
VARBINARY (4000))
GO
INSERT INTO
tcrypt(id, name, encryptdata)
VALUES (1,
'testName’, ENCRYPTBYCERT (CERT_ID(’testcert’), 'testName’))
GO
SELECT id,
name, CONVERT (VARCHAR, DECRYPTBYCERT (CERT_ID (’TESTCERT’), encryptdata))
FROM tcrypt
GO
SELECT id,
name, CONVERT (VARCHAR, encryptdata)
FROM tcrypt
GO
USE master
GO
