in Search

Do you Twitter? Follow us @CodeBetter

Raymond Lewallen

Professional Learner

Create a web service directly from Sql Server 2005 using an HTTP Endpoint

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

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

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

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

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!

Published Jun 23 2005, 10:08 PM by Raymond Lewallen

Comments

Geoff Appleby said:

Dude, very cool. Especialy the sample in VB - there's too much C# around *grin* It's a shame that it comes back as DataSet though - I wonder if there's a way to get it to send me back a DataReader instead.

# June 24, 2005 2:59 AM

OdeToCode Links said:

Kimberly Tripp has 8 steps to improve transaction log throughput.
Thom Robbins has a video to demo...

# June 26, 2005 8:14 PM

Ignat Andrei said:

There is no ned for IIS ? So I assume that with "http.sys module in the Win2K3 kernel" I can program my own WebService?
What will be the future of IIS, then?

# June 27, 2005 2:17 PM

Raymond Lewallen said:

Ingat,

Yes you can program your own web service. As for the future of IIS, I don't expect it to go away anytime soon. You still need a centralized service for handling multiple protocols. Http.sys is just one of those protocols. IIS helps with the setup, configurations and managements of services that utilize protocols such as HTTP, SSL, SMTP and FTP. I think it will be around for awhile.

# June 28, 2005 4:50 AM

Vinod Kumar's Blog said:

# July 4, 2005 5:13 AM

Michael Mileos said:

Hi,

I am trying to consume an HTTP Endpoint with SQL 2005 Reporting Services.

Since reporting services can take either an XML file or a WebService, it seems to make sense that you should be able to use an HTTP Endpoint.

With a WebService.asmx, you can call the WebMethods in the following way:
http://server/Webservice.asmx/WebMethod?Param=Value

I have tried this syntax with the endpoint, but have had no luck.

Do you know how I can render out a DataSet in XML form directly from an Endpoint?

Is this what you meant in the quote below?

> 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!

Regards,

Michael Mileos

# August 3, 2005 12:38 AM

Fernando Luis said:

Hi,

I´m having some troubles when trying to consume the endpoint. I followed the sugestions and create a simple VB.Net program to test the service. But when it runs, the following error appers:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.xml.dll Additional information: There is an error in XML document

Does anybody have a clue ???

Regards
Fernando

# December 23, 2005 7:51 AM

Matt Neerincx {MSFT] said:

What version of VS.NET are you using?

If you are using version before VS.NET 2005 create a web reference to the endpoint using ->

http://localhost/Employee?wsdlsimple">http://localhost/Employee?wsdlsimple

instead of:

http://localhost/Employee?wsdl

If this does not work, add the following section to your "MyTestProgram.exe.config" file to trace out the SOAP request and response to c:\soaptrace.log ->

<system.diagnostics>
<trace autoflush="true" />
<sources>
<source name="System.Net">
<listeners>
<add name="System.Net"/>
</listeners>
</source>
<source name="System.Net.Sockets">
<listeners>
<add name="System.Net"/>
</listeners>
</source>
<source name="System.Net.Cache">
<listeners>
<add name="System.Net"/>
</listeners>
</source>
</sources>
<sharedListeners>
<add
name="System.Net"
type="System.Diagnostics.TextWriterTraceListener"
initializeData="c:\soaptrace.log"
/>
</sharedListeners>
<switches>
<add name="System.Net" value="Verbose" />
<add name="System.Net.Sockets" value="Verbose" />
<add name="System.Net.Cache" value="Verbose" />
</switches>
</system.diagnostics>

Then post this back to this thread, thanks!

# March 20, 2006 2:29 PM

Nathan said:

Hi,

Nice walkthrough, however when I come to implement the code for the windows application I'm having trouble with the DirectCast() function on the webmethod. The error is;

Value of type '1-dimensional array of object' cannot be converted to 'System.Data.Dataset'

Any ideas?

thanks, Nathan

# March 6, 2007 7:15 PM

Kingsley said:

Morning!

Excellent Walkthrough... I too had the '1-dimensional array of object' cannot be converted to 'System.Data.Dataset' , but managed to get around it as follows(Datagrid view is easier to use, imho);

' Create a new instance of the web service

Dim employeesProxy As boomer1.SQLEP_BoomerSelectTest1 = New boomer1.SQLEP_BoomerSelectTest1

' 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

Dim goat As System.Array

goat = employeesProxy.BoomerSelectTest1(TextBox1.Text.Trim)

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

ListBox1.DataSource = goat(0).tables.item(0)

ListBox1.DisplayMember = "Column 0"

DataGridView1.DataSource = goat(0).tables.item(0)

Label1.Text = "Returned Rowcount = " & goat(1).count.ToString

# May 16, 2007 9:12 AM

RL said:

For Geoff Appleby :

You can have it returning other return type. Just his example uses a stored procdure which will return a dataset by default.

# June 27, 2007 11:11 AM

Gonzalo Ruiz said:

I made one CLR that invokes the endpoint webservice

But I need to have URL property as a parameter

that means that when I call with another webservice It does not work

# August 13, 2007 7:01 PM

Austin E. said:

Great Job

# August 14, 2007 3:06 PM

steph said:

Regarding the the "'1-dimensional array of object' cannot be converted to 'System.Data.Dataset' " problem, here's what to do. When creating your endpoint, you need to have this line (see sample below): FORMAT = ROWSETS_ONLY. If you have this line, the web service returns a dataset; otherwise, it returns an object array.

Good luck.

Steph

Sample:

CREATE ENDPOINT SAC

STATE = STARTED

AS HTTP

(

SITE = 'localhost',

PATH = '/SAC',

AUTHENTICATION = ( INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 2000

)

FOR SOAP

(

WEBMETHOD 'GetItemAvailableQty'

(

NAME = 'SAC_BackEnd.dbo.uspGetItemAvailableQty',

SCHEMA = STANDARD,

FORMAT = ROWSETS_ONLY

WSDL = DEFAULT,

BATCHES = ENABLED,

DATABASE = 'SAC_BackEnd'

)

# September 9, 2007 2:45 PM

steph said:

Re: the error "Value of type '1-dimensional array of object' cannot be converted to 'System.Data.Dataset'", when you create your ENDPOINT, you have to have the line "FORMAT = ROWSETS_ONLY" as shown in my example below. This line tells the web service to return a dataset instead of an object array.

Good luck.

CREATE ENDPOINT SAC

STATE = STARTED

AS HTTP

(

SITE = 'localhost',

PATH = '/SAC',

AUTHENTICATION = ( INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 2000

)

FOR SOAP

(

WEBMETHOD 'GetItemAvailableQty'

(

NAME = 'SAC_BackEnd.dbo.uspGetItemAvailableQty',

SCHEMA = STANDARD,

FORMAT = ROWSETS_ONLY

WSDL = DEFAULT,

BATCHES = ENABLED,

DATABASE = 'SAC_BackEnd'

)

# September 9, 2007 6:52 PM

Jorge Ruiz said:

change this line adding (0) after EmployeeList.

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

# September 15, 2007 8:25 PM

Christian Gottardo Jadjiski said:

Do you know if its possible to call a method (by interface or not) implemented in a Windows Application, developed in C#, instead of a procedure or function implemented in SQL?

I need to do a WebService like this one, with EndPoint, because my priority is to pass and receive an XML throw this WebService whitout IIS, and at the same time I need to treat all the process with log and tool for debbug, so I need to implement one Windows Application, with an Arquiteture the can make possible to do this logs and treats exceptions.

Can you help me, please?

Thanks

# December 4, 2007 10:26 AM

Ahamed said:

Only select type of sp we can able to make as webservice? or we can even insert sp also as web service?

# June 19, 2008 10:01 AM

the rasx() context » Blog Archive » Pulling a Bunch of SQL Server and LINQ Links said:

Pingback from the rasx() context » Blog Archive » Pulling a Bunch of SQL Server and LINQ Links

# December 3, 2008 11:35 AM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

About Raymond Lewallen

Working primarily in the public sector during his career, Raymond has designed and built several high profile enterprise level applications for all levels of the government. Raymond now works for MTM Recognition. Raymond is a Microsoft MVP C# and also president of the Oklahoma Agile Developers Group. Raymond spends a lot of his time learning and teaching such things as lean methodologies, Behavior Driven Design, Domain Driven Design, Design Patterns and Extreme Programming practices and principles, to name a few.