Create a webservice directly from Sql Server 2005 using HTTP

Create stored procedure to return list of employeesuse adventureworks
go

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

The Sql 2005 code to create the HTTP ENDPOINTuse adventureworks
go

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'
)

A VB.net form that loads the results of the stored procedure into a list box.

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

 

http://codebetter.com/blogs/raymond.lewallen/archive/2005/06/23/65089.aspx

--