David Hayden [MVP C#]

SQL Server 2005 has this new feature that allows you to expose XML Web Services from the database without the need for IIS. This is really cool to play with, because one can expose a stored procedure via a web service that you can then consume in your client applications. I wrote a short article if you are interested:

• Native XML Web Services in SQL Server 2005 - Creating HTTP Endpoints

However, I wanted to see if I could create an HTTP Endpoint using SQL Server Management Objects.  The goal is to roughly accomplish the same thing as the following:

CREATE ENDPOINT AW_Contacts
    STATE = Started
AS HTTP
    (
        PATH = '/Contacts',
        AUTHENTICATION = (INTEGRATED),
        PORTS = (CLEAR),
        SITE = '*'
    )
FOR SOAP
    (
        WEBMETHOD 'GetContacts'
            (NAME = 'AdventureWorks.dbo.GetContacts'),
        WSDL = DEFAULT,
        DATABASE = 'AdventureWorks',
        NAMESPACE = DEFAULT
    )

Briefly, this command essentially exposes a stored procedure, called GetContacts, as a web method that I created in the AdventureWorks Database.

The cool thing is that you can pull this off in SMO, and below is some example code.

// Connect to Local Instance
Server server = new Server();

// Create New Endpoint
Endpoint newEndpoint = new Endpoint(server,"AW_Contacts");

newEndpoint.ProtocolType = ProtocolType.Http;
newEndpoint.Protocol.Http.HttpAuthenticationModes = HttpAuthenticationModes.Integrated;
newEndpoint.Protocol.Http.HttpPortTypes = HttpPortTypes.Clear;
newEndpoint.Protocol.Http.WebSite = "*";
newEndpoint.Protocol.Http.WebSiteUrlPath = "/Contacts";

newEndpoint.EndpointType = EndpointType.Soap;

newEndpoint.Payload.Soap.DefaultDatabase = "AdventureWorks";
newEndpoint.Payload.Soap.XmlFormatOption = XmlFormatOption.XmlFormat;
newEndpoint.Payload.Soap.XsdSchemaOption = XsdSchemaOption.Standard;
newEndpoint.Payload.Soap.WsdlGeneratorOption = WsdlGeneratorOption.Procedure;
newEndpoint.Payload.Soap.WsdlGeneratorProcedure =
            "[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]";

// Create new Soap Method
SoapPayloadMethod method = new SoapPayloadMethod(newEndpoint.Payload.Soap, "GetContacts");
method.MethodXsdSchemaOption = MethodXsdSchemaOption.Standard;
method.ResultFormat = ResultFormat.RowSets;
method.SetSqlMethod("AdventureWorks", "dbo", "GetContacts");
method.MethodLoginType = MethodLoginType.Windows;

newEndpoint.Payload.Soap.SoapPayloadMethods.Add(method);

newEndpoint.Create();
newEndpoint.Start();

SQL Server 2005 XML Web Services and SQL Server Management Objects are just plain cool.

SQL Server Management Objects Resources:

• SetDefaultInitFields and SQL Server Management Objects ( SMO ) - Delayed Instantiation of Object Properties
• SQL Server Management Objects (SMO) - Create Stored Procedures in C# - Code Generation
• Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation
• Generate a Table Create Script using SQL Server Management Objects ( SMO )