This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Create a web service directly from Sql Server 2005 using an HTTP Endpoint Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

What is an HTTP Endpoint in Sql Server?  Well, it is a way to create a usable interface using HTTP or TCP for SOAP, T-Sql, Service Brokering and a few other things.  I’m just going to tell you, quick and simple, about creating a web service to return data, although you can return scalar values, messages and errors too.  The results returned are serialized into Xml.  If you have Windows 2003, you don’t have to have IIS installed.  Sql server will use the http.sys module in the Win2K3 kernel.

So lets look at creating a usable web service from within Sql Server.  Lets start with creating a stored procedure.

create procedure dbo.GetEmployees
As
select e.employeeid, e.title, c.FirstName + ‘ ‘ + c.Lastname As Fullname from HumanResources.employee e
inner join person.contact c
on e.contactid = c.contactid
go

Now, lets create our sql server web service, known as an HTTP ENDPOINT.

CREATE ENDPOINT GetEmployees
    STATE = STARTED
AS HTTP
(
    PATH = '/Employee',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR),
    SITE = 'localhost'
)
FOR SOAP
(
    WEBMETHOD 'EmployeeList'
        (NAME='AdventureWorks.dbo.GetEmployees'),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = 'AdventureWorks',
    NAMESPACE = 'http://AdventureWorks/Employee'
)
go

There we go.  We now have a web service!  You access and use this endpoint the same way you would any other web service.  You can create multiple WEBMETHODs in a single endpoint, just seperate them with commas in the FOR SOAP statement.

Here are the values you can use for the â€œSTATE” argument:

• STARTED—listening and responding
• DISABLED—neither listening nor responding
• STOPPED—listening, but returns errors to client requests

Here are the “AS HTTP” arguments you can use:

• Path –  The virtual URL path on the server where the Web service will reside
• Authentication 
• INTEGRATED – most secure. It will try to use Kerberos-based authentication if possible (otherwise, NTLM).
• DIGEST is not as secure as INTEGRATED. You should use it only if INTEGRATED authentication is not possible.
• BASIC authentication is the least secure. You should use it only if you can't implement either INTEGRATED or DIGEST authentication methods. BASIC requires SSL as the Port value.
• Ports –  CLEAR (HTTP - port 80 by default) SSL (HTTPS - port 443 by default)
• Site –  The name of the server on which the Web service is running

So, now lets put our endpoint to work.  First, create a new windows application project, and add a web reference to it.  When you browse for the web service, it won’t be discovered automatically.  You have to type in the url and click “go”.  The url in this case is http://localhost/Employee?wsdl.  You’ll see the EmployeeList method come up in the list, just like using any other web service.  Go ahead and add the service and rename it to whatever.  I called mine “adventureWorksService”.

Now we just add code like using any other webservice.  I’ve added a button to click to populate the listbox on my form.  So here it is:

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' Create a new instance of the web service

        Dim employeesProxy As adventureWorksService.GetEmployees = New adventureWorksService.GetEmployees

        ' You have to pass in credentials to authenticate yourself to use the service.  We are just going to use

        '   the same credentials we have logged into our computer as.

        employeesProxy.Credentials = System.Net.CredentialCache.DefaultCredentials

        ' The result of a SELECT statement via an endpoint can be converted to a DataSet

        Dim ds As System.Data.DataSet = DirectCast(employeesProxy.EmployeeList, DataSet)

        ListBox1.DataSource = ds.Tables(0)

        ListBox1.DisplayMember = "FullName"

        ListBox1.ValueMember = "EmployeeId"

    End Sub

End Class

I am having problems with my Adobe Photoshop, so I don’t have any screenshots to show you.  You’ll just have to trust me.

Here are the other return values from the endpoint:

• Select statement – Convert to DataSet
• Select statement FOR XML – Convert to XmlElement
• Error or Message â€“ WSDL SqlMessage
• Output parameter – WSDL SqlParameter
• Row count – WSDL SqlRowCount
• RETURN statement – Convert to Int32

Tada!  There you go!  A web service straight from Sql Server!