CREATE DATABASE Repairs
use Repairs

CREATE LOGIN [Canopus5\MiniDan] FROM WINDOWS

CREATE Function RepairEstimate(@model NVARCHAR(10))
RETURNS INT
AS
BEGIN
RETURN 12
END

CREATE USER [Canopus5\MiniDan]
GRANT EXECUTE ON RepairEstimate TO [Canopus5\MiniDan]

EXEC sp_delete_http_namespace_reservation N'http://Canopus5:80/Repairs/req'

EXEC sp_reserve_http_namespace N'http://Canopus5:80/Repairs/req' 

--DROP ENDPOINT RepairPoint1

CREATE ENDPOINT RepairPoint
STATE  = Started
AS HTTP
(
PATH = '/Repairs/req',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetEstimate'
(NAME = 'Repairs.dbo.RepairEstimate', SCHEMA=STANDARD),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)

use master
GRANT CONNECT ON ENDPOINT::RepairPoint1 TO [Canopus5\MiniDan]




// for examples

EXEC sp_reserve_http_namespace N'http://Canopus5:80/Repairs/Hours'
EXEC sp_reserve_http_namespace N'http://Canopus5:80/Accounting'
 

SELECT E.name, E.type_desc,
E.state_desc, H.site, 
H.url_path, H.clear_port
FROM sys.endpoints AS E
INNER JOIN sys.http_endpoints AS H
ON E.endpoint_id = H.endpoint_id


select * from sys.http_endpoints
select * from sys.endpoints


SELECT *
FROM sys.endpoints AS E
INNER JOIN sys.http_endpoints AS H
ON E.endpoint_id = H.endpoint_id


--select * from sys.ip_exceptions
select * from sys.soap_endpoints
select * from sys.endpoint_webmethods
select * from sys.endpoints

setuser 'JoeSqlUser'
EXEC sp_reserve_http_namespace N'http://Canopus5:80/Repairs/Inventory'


/****** Object:  Endpoint [RepairPoint]    Script Date: 11/20/2005 15:55:32 ******/
CREATE ENDPOINT [RepairPoint] 
	AUTHORIZATION [CANOPUS5\Administrator]
	STATE=STARTED
	AS HTTP (PATH=N'/Repairs/req', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED), SITE=N'*', CLEAR_PORT = 80, COMPRESSION=DISABLED)
	FOR SOAP (
WEBMETHOD 'urn:autos'.'GetEstimate'( NAME=N'[Repairs].[dbo].[RepairEstimate]'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'Repairs', NAMESPACE=N'urn:other', SCHEMA=STANDARD, CHARACTER_SET=XML)


CREATE 
--ALTER
PROC Names
AS
SELECT * FROM (
SELECT 1 AS ID, 'Joe' AS NAME
UNION
SELECT 2 AS ID, 'Jane' AS NAME
) AS D
FOR XML RAW

--DROP ENDPOINT Personell
exec Names


CREATE ENDPOINT Personell
STATE  = Started
AS HTTP
(
PATH = '/HR',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetPersonell'
(NAME = 'Repairs.dbo.Names', SCHEMA=STANDARD),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)

use master
GRANT CONNECT ON ENDPOINT::RepairPoint1 TO [Canopus5\MiniDan]

