Web services and SQL Server 2005

Question

Is it possible to set up SQL Server with a web service to receive and store data bypassing the IIS/ASP.NET layer altogether?

I know it's possible to set up a web service via HTTP End Points and such to return requested data but I can't seem to find examples to show the opposite process.

Answer 1

Hi Nai,

Following URLs will help to understand the setup process.

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

http://www.devx.com/dbzone/Article/28525/0/page/1

Stored Procedure

use 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

Creating EndPoint

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

Answer 2

Yes, an example here (devx) and MSDN article

Edit:

Nai found out it's deprecated after SQL Server 2008. This other BOL article says:

Plan to convert your existing SOAP/HTTP endpoints to use Windows Communications Foundation (WCF) or ASP.NET.

Answer 3

The answer - if you don't want to go with deprecated bits - is to use WCF.

If you need to do new "web-services" from MS applications you should be using WCF (though its slightly more stressful than asp.net web services - its also a lot more capable). I've put "web-services" in quotes because you're not limited to http based protocols with WCF.

In this context the key point is that WCF applications can be self-hosting so you're reducing your dependency on IIS. I think that possibly a clearer statement of requirements - you obviously have specific constraints you're trying to address - might lead to a better answer