Publish SQL Server 2005 As Webservice

Introduction

In this article, I will explain how we can publish a SQL Server 2005 stored procedure as a web service and access this web service from a Microsoft VB.NET Windows application.

Background

Microsoft started addressing the need of XML support for SQL Server from the release of SQL Server 2000 version. The initial version of SQL Server 2000 shipped with many XML features. Later on, Microsoft released SQLXML 3.0 which provided the facility to publish TSQL batch statement, SQL stored procedures and SQL functions as webservices.

We no longer require SQLXML 3.0 since SQL Server 2005 version supports this natively.

Using Northwind Database

I have used the Northwind database to create the stored procedure used in this article. 2005 version of the SQL Server doesn't come with Northwind and pub database. However you can download the Northwind and pub database from this link.

The download contains the *.mdf and *.ldf files. You can attach this to your SQL Server 2005.

How to Publish your Stored Procedure as a Webservice?

Publishing your stored procedure as webservice is very easy and involves simple steps.

I created a stored procedure named GetProducts this will accept product id as parameter. This procedure will retrieve the information from the products table based on the ProductId supplied.

Create PROC GetProduct
(
@ProductId int
)
As
Select * from 
Products 
where
ProductId =@ProductId

In the next step, we will see how we can publish GetProduct as a webservice. We will use CREATE ENDPOINT TSQL statement for this. Endpoints are objects that represent communication between a server and client or the point through which SQL Server communicates with the outside world.

SQL Server 2005 automatically creates an Endpoint for each of the four protocols (TCP/IP, Shared Memory, Named Pipe, VIA) that accept TDS connections. The HTTP endpoint type will enable the SQL Server to provide support for Webservices.

To expose our stored procedure as Webservice, we will create an HTTP endpoint and publish the stored procedure as a web method to it.

CREATE ENDPOINT WebServiceTest
AUTHORIZATION [sa]
STATE = STARTED
AS HTTP
(
AUTHENTICATION = (INTEGRATED),
PATH = '/MyTestWebservicepath/',
PORTS = (CLEAR),
CLEAR_PORT = 8045,
SITE = '*',

)
FOR SOAP
(
WEBMETHOD 'urn:www.codeproject.com'.'GetProduct'
(
NAME = 'Northwind.dbo.GetProduct',
SCHEMA = STANDARD,
FORMAT = ALL_RESULTS
),
WSDL = DEFAULT,
BATCHES = DISABLED,
SCHEMA = STANDARD,
LOGIN_TYPE = WINDOWS,
SESSION_TIMEOUT = 100,
DATABASE = 'Northwind',
NAMESPACE = 'www.codeproject.com',
CHARACTER_SET = XML
)

You can add any number of stored procedures to an HTTP endpoint.

Following is the syntax for adding a new stored procedure as web method to an existing Endpoint:

ALTER ENDPOINT sql_endpoint FOR SOAP 
(ADD WEBMETHOD 'YourWebMethod' (name='Northwind.dbo.YourWebMethod'));

For Your Reference

Following is the syntax for the create endpoint:

CREATE ENDPOINT Name-Of-The-Endpoint 
[AUTHORIZATION login information to sqlserver   ]
STATE = { STARTED | STOPPED | DISABLED }
AS HTTP
(
PATH = 'PATH-TO-ACCES-WEBSERVICE-(YOUR SERVER NAME\PATH WILL BE THE ACTUAL URL)', 
AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] ), 
PORTS = ( { CLEAR | SSL} [ ,... n ] )
[ SITE = {'*' | '+' | 'WEBSITE' },]
[, CLEAR_PORT = CLEARPORT ]
[, SSL_PORT = SSLPORT ]
[, AUTH_REALM = { 'REALM' | NONE } ]
[, DEFAULT_LOGON_DOMAIN = { 'DOMAIN' | NONE } ]
[, RESTRICT_IP = { NONE | ALL } ]
[, COMPRESSION = { ENABLED | DISABLED } ]
[, EXCEPT_IP = (ALL | ( 4-part-ip ) | ( "ip_address_v6" ) )
)
FOR SOAP
(
[ { WEBMETHOD [ NAMESPACE.] 'method_alias'
( NAME = 'database.Schema.name'
[ , SCHEMA = { NONE | STANDARD | DEFAULT } ]
[ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]
) 
} [ ,...n ] ]
[ BATCHES = { ENABLED | DISABLED } ]
[ , WSDL = { NONE | DEFAULT | 'PROCEDURE NAME' } ]
[ , SESSIONS = { ENABLED | DISABLED } ]
[ , LOGIN_TYPE = { MIXED | WINDOWS } ]
[ , SESSION_TIMEOUT = TIME-INRTERVEL | NEVER ]
[ , DATABASE = { 'DATABASE-NAME' | DEFAULT }
[ , NAMESPACE = { 'namespace' | DEFAULT } ]
[ , SCHEMA = { NONE | STANDARD } ]
[ , CHARACTER_SET = { SQL | XML }]
[ , MAX_SOAP_HEADERS_SIZE = { int | DEFAULT }]
)

Accessing SQL Server Webmethod from the VB.NET Application

We will create a new VB.NET application to access the webservice which we created in this example.

In the newly created application, add a web reference:

Type the URL http://WEBSERVER:[PORT-NUMBER]/PATH?wsdl.

I used http://localhost:8045/MyTestWebservicepath?wsdl since 8045 is my HTTP port and MyTestWebservicepath is the path parameter mentioned in the create endpoint statement.

Enter web reference name as Test.

Add a Windows Form to the project and place a Button and a TextBox control.

In the button click, write the following code:

//Create the instance of Web service Proxy
Dim MywebTest As New Test.WebServiceTest
//Object array for holding the result from the webserver 
Dim dtsetArray() As Object
//Calling the Web Services
dtsetArray = MywebTest.GetEmployeeBasics(TextBox1.Text)
For Each obj As Object In dtsetArray
If obj.GetType Is GetType(DataSet) Then
dt = CType(obj, DataSet)
MsgBox(dt.Tables(0).Rows(0)(1))
End If
Next

The web webservice will return an array of objects which consist of the result dataset and a SQL resultcount which will give you the rows count.

Hope this will be a starting point for your SQL Server 2005 webservices.

Please leave your suggestions for improving this article.

History

• 30^th September, 2007: Initial post