CREATE DATABASE Repairs
Go
use Repairs

CREATE 
--ALTER
Function RepairEstimate(@model NVARCHAR(10) NOT NULL)
RETURNS INT
AS
BEGIN
RETURN NULL
END
Go


CREATE 
Function RepairEstimate2(@model NVARCHAR(10))
RETURNS INT
AS
BEGIN
RETURN 12 + @m
END
Go

CREATE 
--ALTER 
PROC RepairEstimate3(@model NVARCHAR(10), @m INT OUTPUT, @s NVARCHAR(20) OUTPUT)
AS
SET @m = @m + 12
SET @s = 'asdf'




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

--DROP ENDPOINT RepairPoint

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),
WEBMETHOD 'urn:autos'.'GetEstimate2'
(NAME = 'Repairs.dbo.RepairEstimate2', SCHEMA=STANDARD),
WEBMETHOD 'urn:autos'.'GetEstimate3'
(NAME = 'Repairs.dbo.RepairEstimate3', SCHEMA=STANDARD),
WSDL='Repairs.dbo.GenerateRepairWSDL',
--WSDL = 'Repairs.dbo.RepairsWSDL',
SCHEMA=STANDARD,
SESSIONS=ENABLED,
SESSION_TIMEOUT = NEVER,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)

--ALTER ENDPOINT RepairPoint STATE = DISABLED
--ALTER ENDPOINT RepairPoint STATE = STARTED
--ALTER ENDPOINT RepairPoint STATE = STOPPED



SELECT * from sys.http_endpoints
SELECT * from sys.endpoints
SELECT * from sys.endpoint_webmethods

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

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



USE master
EXEC sp_grantlogin @loginame='Canopus5\Administrator'
EXEC sp_grantdbaccess @loginame='Canopus5\Administrator'
GRANT CONNECT ON ENDPOINT::RepairPoint TO [Canopus5\Administrator]
REVOKE CONNECT ON ENDPOINT::RepairPoint TO [Canopus5\Administrator]


CREATE LOGIN [Canopus5\MiniDan] FROM WINDOWS
use Repairs

CREATE USER [Canopus5\MiniDan]
DROP USER [Canopus5\MiniDan]
use master

GRANT CONNECT ON ENDPOINT::RepairPoint TO [Canopus5\MiniDan]
use Repairs

REVOKE CONNECT ON ENDPOINT::RepairPoint TO Public


select * from sys.http_endpoints

select * from sys.dm_exec_sessions


exec RepairsWSDL 0,0,'asdf', 'asdf', 'asdf'

exec sys.sp_http_generate_wsdl_defaultsimpleorcomplex
65550,0,'asdf', 'wsdl', 'asdf'

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


CREATE 
--ALTER
FUNCTION GetWSDLUrl(
@endpointID as INT,
@host NVARCHAR(256),
@isSSL as bit
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @vdir NVARCHAR(MAX);
DECLARE @clear_port NVARCHAR(5);
DECLARE @ssl_port NVARCHAR(5);
DECLARE @url_path NVARCHAR(MAX);
SELECT @clear_port = CAST(clear_port AS NVARCHAR(5)),
 @ssl_port = CAST(ssl_port AS NVARCHAR(5)), @url_path = url_path
FROM sys.http_endpoints WHERE endpoint_id = @endpointID
IF @isSSl = 0
SELECT @vdir ='http://' + @host + ':' + @clear_port + @url_path;
ELSE
SELECT @vdir ='https://' + @host + ':' + @ssl_port + @url_path;
RETURN @vdir;
END

SELECT dbo.GetWSDLUrl(65562, 'MyHost', 0)



SELECT endpoint_id, clear_port, 
ssl_port, url_path FROM sys.http_endpoints
WHERE endpoint_id = 65562





CREATE 
--ALTER
PROC RepairsWSDL
(@endpointID as int,
@isSSL as bit,
@host as nvarchar(256),
@queryString as nvarchar(256),
@userAgent as nvarchar(256) 
) AS
DECLARE @vdir NVARCHAR(MAX);
/*
DECLARE @clear_port NVARCHAR(5);
DECLARE @ssl_port NVARCHAR(5);
DECLARE @url_path NVARCHAR(MAX);
SELECT @clear_port = CAST(clear_port AS NVARCHAR(5)),
 @ssl_port = CAST(ssl_port AS NVARCHAR(5)), @url_path = url_path
FROM sys.http_endpoints WHERE endpoint_id = @endpointID
IF @isSSl = 0
SELECT @vdir ='http://' + @host + ':' + @clear_port + @url_path;
ELSE
*/
SELECT @vdir = dbo.GetWSDLUrl(@endpointID, @host, @isSSL, @queryString);
DECLARE @wsdl XML;
SET @wsdl = 
'<wsdl:definitions xmlns:tns="urn:other" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:s0="urn:autos" targetNamespace="urn:other" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
  <wsdl:types>
    <schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="urn:autos" xmlns="http://www.w3.org/2001/XMLSchema">
      <xsd:element name="GetEstimate">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element minOccurs="1" maxOccurs="1" name="model" nillable="true">
              <xsd:simpleType>
                <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="10" />
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:element name="GetEstimateResponse">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element minOccurs="1" maxOccurs="1" name="GetEstimateResult" nillable="true" type="xsd:int" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </schema>
  </wsdl:types>
  <wsdl:message name="s0MsgGetEstimateSoapIn">
    <wsdl:part name="parameters" element="s0:GetEstimate" />
  </wsdl:message>
  <wsdl:message name="s0MsgGetEstimateSoapOut">
    <wsdl:part name="parameters" element="s0:GetEstimateResponse" />
  </wsdl:message>
  <wsdl:portType name="RepairPointSoap">
    <wsdl:operation name="GetEstimate">
      <wsdl:input name="s0MsgGetEstimateSoapIn" message="tns:s0MsgGetEstimateSoapIn" />
      <wsdl:output name="s0MsgGetEstimateSoapOut" message="tns:s0MsgGetEstimateSoapOut" />
    </wsdl:operation>
  </wsdl:portType>
  <wsdl:binding name="RepairPointSoap" type="tns:RepairPointSoap">
    <soap:binding transport="http://schemas.xmlsoap.org/soap/http" />
    <wsdl:operation name="GetEstimate">
      <soap:operation soapAction="urn:autosGetEstimate" style="document" />
      <wsdl:input name="s0MsgGetEstimateSoapIn">
        <soap:body use="literal" />
      </wsdl:input>
      <wsdl:output name="s0MsgGetEstimateSoapOut">
        <soap:body use="literal" />
      </wsdl:output>
    </wsdl:operation>
  </wsdl:binding>
  <wsdl:service name="RepairPoint">
    <wsdl:port name="RepairPoint" binding="tns:RepairPointSoap">
      <soap:address location="http://canopus5:80/Repairs/req" />
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>'
/*
SET @wsdl = '<wsdl:definitions xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" 
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:s0="urn:autos" xmlns:tns="urn:other" targetNamespace="urn:other">
	<wsdl:types>
		<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="urn:autos">
			<xsd:element name="GetEstimate">
				<xsd:complexType>
					<xsd:sequence>
						<xsd:element minOccurs="1" maxOccurs="1" name="model" nillable="true">
							<xsd:simpleType>
								<xsd:restriction base="xsd:string">
									<xsd:maxLength value="10"></xsd:maxLength>
								</xsd:restriction>
							</xsd:simpleType>
						</xsd:element>
					</xsd:sequence>
				</xsd:complexType>
			</xsd:element>
			<xsd:element name="GetEstimateResponse">
				<xsd:complexType>
					<xsd:sequence>
						<xsd:element minOccurs="1" maxOccurs="1" name="GetEstimateResult" type="xsd:int" nillable="true"></xsd:element>
					</xsd:sequence>
				</xsd:complexType>
			</xsd:element>
		</xsd:schema>
	</wsdl:types>
	<wsdl:message name="s0MsgGetEstimateSoapIn">
		<wsdl:part name="parameters" element="s0:GetEstimate"></wsdl:part>
	</wsdl:message>
	<wsdl:message name="s0MsgGetEstimateSoapOut">
		<wsdl:part name="parameters" element="s0:GetEstimateResponse"></wsdl:part>
	</wsdl:message>
	<wsdl:portType name="RepairPointSoap">
		<wsdl:operation name="GetEstimate">
			<wsdl:input name="s0MsgGetEstimateSoapIn" message="tns:s0MsgGetEstimateSoapIn"></wsdl:input>
			<wsdl:output name="s0MsgGetEstimateSoapOut" message="tns:s0MsgGetEstimateSoapOut"></wsdl:output>
		</wsdl:operation>
	</wsdl:portType>
	<wsdl:binding name="RepairPointSoap" type="tns:RepairPointSoap">
		<soap:binding transport="http://schemas.xmlsoap.org/soap/http" style="document"/>
		<wsdl:operation name="GetEstimate">
			<soap:operation soapAction="urn:autosGetEstimate" style="document"></soap:operation>
			<wsdl:input name="s0MsgGetEstimateSoapIn">
				<soap:body use="literal"/>
			</wsdl:input>
			<wsdl:output name="s0MsgGetEstimateSoapOut">
				<soap:body use="literal"/>
			</wsdl:output>
		</wsdl:operation>
	</wsdl:binding>
	<wsdl:service name="RepairPoint">
		<wsdl:port name="RepairPoint" binding="tns:RepairPointSoap">
			<soap:address location="http://gggcanopus5/Repairs/req"></soap:address>
		</wsdl:port>
	</wsdl:service>
</wsdl:definitions>';
*/
SET @wsdl.modify('
declare namespace soap="http://schemas.xmlsoap.org/wsdl/soap/";
replace value of (//soap:address[1]/@location)[1] with sql:variable("@vdir")');
SELECT CAST(@wsdl AS NVARCHAR(MAX)) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];



exec sp_configure 'show advanced options', 1
RECONFIGURE
exec sp_configure 'enable http', 0
exec sp_configure 'show advanced options', 0
RECONFIGURE


select * from sys.configurations
ORDER by name

SELECT * from sys.soap_endpoints

SELECT name, wsdl_generator_procedure
from sys.soap_endpoints
where name='RepairPoint'



CREATE 
--ALTER
PROC RepairsWSDL

CREATE 
--ALTER
FUNCTION RepairWSDL
(@endpointID as int,
@isSSL as bit,
@host as nvarchar(256)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @vdir NVARCHAR(MAX);
SELECT @vdir = dbo.GetWSDLUrl(@endpointID, @host, @isSSL);
DECLARE @wsdl XML;
SET @wsdl = 
'<wsdl:definitions xmlns:tns="urn:other" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:s0="urn:autos" targetNamespace="urn:other" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
  <wsdl:types>
    <schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="urn:autos" xmlns="http://www.w3.org/2001/XMLSchema">
      <xsd:element name="GetEstimate">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element minOccurs="1" maxOccurs="1" name="model" nillable="true">
              <xsd:simpleType>
                <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="10" />
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:element name="GetEstimateResponse">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element minOccurs="1" maxOccurs="1" name="GetEstimateResult" type="xsd:int" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </schema>
  </wsdl:types>
  <wsdl:message name="s0MsgGetEstimateSoapIn">
    <wsdl:part name="parameters" element="s0:GetEstimate" />
  </wsdl:message>
  <wsdl:message name="s0MsgGetEstimateSoapOut">
    <wsdl:part name="parameters" element="s0:GetEstimateResponse" />
  </wsdl:message>
  <wsdl:portType name="RepairPointSoap">
    <wsdl:operation name="GetEstimate">
      <wsdl:input name="s0MsgGetEstimateSoapIn" message="tns:s0MsgGetEstimateSoapIn" />
      <wsdl:output name="s0MsgGetEstimateSoapOut" message="tns:s0MsgGetEstimateSoapOut" />
    </wsdl:operation>
  </wsdl:portType>
  <wsdl:binding name="RepairPointSoap" type="tns:RepairPointSoap">
    <soap:binding transport="http://schemas.xmlsoap.org/soap/http" />
    <wsdl:operation name="GetEstimate">
      <soap:operation soapAction="urn:autosGetEstimate" style="document" />
      <wsdl:input name="s0MsgGetEstimateSoapIn">
        <soap:body use="literal" />
      </wsdl:input>
      <wsdl:output name="s0MsgGetEstimateSoapOut">
        <soap:body use="literal" />
      </wsdl:output>
    </wsdl:operation>
  </wsdl:binding>
  <wsdl:service name="RepairPoint">
    <wsdl:port name="RepairPoint" binding="tns:RepairPointSoap">
      <soap:address location="http://canopus5:80/Repairs/req" />
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>'
SET @wsdl.modify('
declare namespace soap="http://schemas.xmlsoap.org/wsdl/soap/";
replace value of (//soap:address[1]/@location)[1] with sql:variable("@vdir")');
RETURN CAST(@wsdl AS NVARCHAR(MAX));
END

SELECT dbo.RepairWSDL(
65562,
0,
'MyHost'
)


CREATE 
--ALTER
PROCEDURE GenerateRepairWSDL
(@endpointID as int,
@isSSL as bit,
@host as nvarchar(256),
@queryString as nvarchar(256),
@userAgent as nvarchar(256) 
) AS
BEGIN
IF @queryString = 'WSDLMinimal'
BEGIN
SELECT dbo.RepairWSDL(@endpointID, @isSSl, @host) AS
[XML_F52E2B61-18A1-11d1-B105-00805F49916B];
RETURN
END
ELSE
BEGIN
EXEC sp_http_generate_wsdl_defaultcomplexorsimple
@endpointID, @isSSL, @host, @queryString, @userAgent;
RETURN
END
raiserror(17885, 16, 1)
END


--XML_F52E2B61-18A1-11d1-B105-00805F49916B 
CREATE 
--ALTER
PROC Locations
AS
DECLARE @m XML;
WITH XMLNAMESPACES ('urn:auto' as auto)
SELECT ID AS [auto:ID], Name AS [auto:Name] from ls AS [auto:Location]
for xml auto, ELEMENTS, ROOT ('auto:Locations')

--AS  [XML_F52E2B61-18A1-11d1-B105-00805F49916B]
SELECT @M

exec locations


WITH XMLNAMESPACES ('urn:auto' as auto)
SELECT ID AS [auto:ID], Name AS [auto:Name] from ls AS [auto:Location]
for xml auto, ELEMENTS, ROOT ('auto:Locations')




CREATE TABLE Ls
(
ID INT,
NAME VARCHAR(50)
)

INSERT INTO Ls VALUES (1, 'Boston')
INSERT INTO LS VALUES (2, 'Los Angeles')


CREATE PROC GenerateLocationsWSDL
AS



CREATE ENDPOINT Locations
STATE  = Started
AS HTTP
(
PATH = '/Locations',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetLocations'
(NAME = 'Repairs.dbo.Locations', SCHEMA=NONE, FORMAT=NONE),
WSDL=NONE,
--WSDL = 'Repairs.dbo.RepairsWSDL',
SCHEMA=NONE,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)


CREATE 
--ALTER
PROC Test5 (@data int output)
AS
SET @data = 5
PRINT 'This is the Test5 message'
RAISERROR ('Test5 error', 10, 1)
SELECT * FROM
(SELECT 1 AS ID, 'Joe' AS NAME
UNION
SELECT 2 AS ID, 'Jane' AS NAME)
AS A


DECLARE @data int
exec TEST5 @data output
select @data

--DROP ENDPOINT Test5

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


CREATE ENDPOINT SQLServer
STATE  = Started
AS HTTP
(
PATH = '/SQL',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other',
BATCHES=ENABLED
)

CREATE PROC 