For creating web service in SQL server you need to understand http end points. So what is http end points ? It provide an interface using TCP or http for SOAP.

Web Services in sql server return rowset data, scalar values , messages and errors. This all can be done withhout IIS.

For creating web service in SQL server you just need to create a stored procdure and http end point.

Lets take an example.

Here we are creating a stored procedure first :-

create procedure dbo.SelectEmployees

As

Select employeeId, EmployeeName from tblEmployees

go

Now we create http end point (web service)

CREATE ENDPOINT SelectEmployees

STATE = STARTED

AS HTTP

(

PATH = ‘/Employee’,

AUTHENTICATION = (INTEGRATED),

PORTS = (CLEAR),

SITE = ‘localhost’

)

FOR SOAP

(

WEBMETHOD ‘EmployeeData’

(NAME=’YourDB.dbo.SelectEmployees’),

BATCHES = DISABLED,

WSDL = DEFAULT,

DATABASE = ‘YourDB’,

NAMESPACE = ‘http://sachinjain.me/Employee’

)

go

Now web service is ready for us. We can use it as normal webservice.

STATE Parameter can have following values :-

STARTED —listening and responding

DISABLED — neither listening nor responding

STOPPED — listening, but returns errors to client requests

In AS HTTP you can use:

Path –  The virtual URL path on the server where the Web service will exist

Authentication
INTEGRATED – most secure.
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.

Ports –  CLEAR (HTTP – port 80 by default) SSL (HTTPS – port 443 by default)

Site –  The name of the server on which your Web service is running

Creating web services in SQL server, 5.0 out of 5 based on 1 rating