Click here to Skip to main content
Click here to Skip to main content

Publish SQL Server 2005 As Webservice

, 30 Sep 2007
Rate this:
Please Sign up or sign in to vote.
In this article, I will explain how we can publish a SQL Server 2005 stored procedure as web service and access the web service from a Microsoft VB.NET Windows application

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:

Screenshot - add-reference.gif

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.

Screenshot - add-Ref2.gif

Enter web reference name as Test.

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

Screenshot - add-Ref3.gif

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

  • 30th September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

rilov
Software Developer (Senior)
Canada Canada
Rilov is working as a software Developer
with maxxam analytics in Canada.
 
rilovpk@gmail.com
 
'I have not failed. I've just found 10,000 ways
that won't work.'
-Thomas Edison

Comments and Discussions

 
QuestionCan Native XML Web Services be used in large multi-layer projects? [modified] Pinmemberhanzq@mainsoft.com.cn7-Oct-07 15:56 
AnswerRe: Can Native XML Web Services be used in large multi-layer projects? Pinmemberrilov8-Oct-07 2:45 
GeneralAm I understanding this correctly PinmemberCary McAnally3-Oct-07 12:03 
GeneralRe: Am I understanding this correctly [modified] Pinmemberrilov3-Oct-07 14:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 30 Sep 2007
Article Copyright 2007 by rilov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid