create HTTP Endpoing (Web Service) in Sql Server 2005/2k5

you must be wondering about HTTP endpoint, i did too, anyhow HTTP Endpoint means way to create service interface using either HTTP or SOAP or TCP means web service🙂. Scalar values, errors, messages can be return in searlized XML format. you don’t need of IIS to deploy HTTP Endpoint or you may say sql server 2005 web service. in win 2k3 u may use HTTP.sys module of kernel for that purpose rather than IIS

Lets come to the implementation:

// Create Stored procedure, which we shall use in our web service

use adventureworks // database name
go

create procedure dbo.GetEmployees //stored proc name
As
select * from employee; //simplest query
go

// code for http endpoint/web service

use 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’
)
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

One response to “create HTTP Endpoing (Web Service) in Sql Server 2005/2k5

  1. I made my own werserver in VB but when i saw this I wanned to create my own webserver on a TCP endpoint in SQL.

    The Database mail option doesn’t make me happy. And in de free technical preview is this option not available.

    I get a response on a telnet session but I can’t ceate the response ‘220 Hello’ on beginning a session. HELP!!!??

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s