Native XML Web Services in SQL Server 2005 - Creating HTTP Endpoints - Native HTTP
by David Hayden ( .NET Developer )
I attended a SQL Server 2005 class the other day that peaked my interest in all the new development features in SQL Server 2005.
According to the product information on the Microsoft website, the following are the top 10 new development features in SQL Server 2005:
TOP 10 New Development Features in SQL Server 2005
- .NET Framework Hosting - With SQL Server 2005 developers will be able to create database objects using familiar languages such as Microsoft Visual C# .NET and Microsoft Visual Basic .NET. Developers will also be able to create two new objects—user-defined types and aggregates.
- XML Technologies - Extensible Markup Language (XML) is an important standard for disseminating data between different applications over local networks and the Internet. SQL Server 2005 will natively support storage and query of XML documents.
- ADO.NET version 2.0 - From new support for SQL Types to Multiple Active Result Sets (MARS), ADO.NET in SQL Server 2005 will evolve dataset access and manipulation to achieve greater scalability and flexibility.
- Security Enhancements - A new security model in SQL Server 2005 will separate users from objects, provide fine-grain access, and enable greater control of data access. Additionally, all system tables will be implemented as views, providing more control over database system objects.
- Transact-SQL Enhancements - SQL Server 2005 provides new language capabilities for developing scalable database applications. These enhancements include error handling, recursive query capabilities, relational operator PIVOT, APPLY, ROW_NUMBER and other row ranking functions, and more.
- SQL Service Broker - SQL Service Broker will provide a distributed, asynchronous application framework for large-scale, line-of-business applications.
- Notification Services - Notification Services enables businesses to build rich notification applications that deliver personalized and timely information, such as stock market alerts, news subscriptions, package delivery alerts, and airline ticket prices, to any device. With SQL Server 2005, Notification Services is more tightly integrated with technologies such as Analysis Services and SQL Server Management Studio.
- Web Services - With SQL Server 2005 developers will be able to develop Web services in the database tier, making SQL Server a hypertext transfer protocol (HTTP) listener and providing a new type of data access capability for Web services-centric applications.
- Reporting Services - With SQL Server 2005, Reporting Services will provide report controls that will be shipped with Visual Studio 2005. Integrated reporting controls will provide improved reporting capabilities for enterprise applications.
- Full-Text Search Enhancements - SQL Server 2005 will support rich, full-text search applications. Cataloging capabilities will be enhanced to provide greater flexibility over what is cataloged. Query performance and scalability will be improved dramatically, and new management tools will provide greater insight into the full-text implementation.
Over the next few weeks I thought I would educate myself a bit better on these subjects and share my thoughts on my blog, starting with the new native XML Web Services that is a part of SQL Server 2005.
Native XML Web Services in SQL Server 2005
To begin, you need to be running Windows 2003 or Windows XP SP2 to use this feature and you don't need IIS. In fact, if you are running IIS, it may cause a problem if both SQL Server 2005 and IIS are both listening to port 80 when you try to create your HTTP Endpoint. For this tutorial, I would just stop IIS if you have it running. If you don't want to do that, you will need to change the default port that either IIS or SQL Server 2005 is using to listen to HTTP Requests. The default is 80, and one of them will need to be changed.
Expose Stored Procedure as XML Web Service
My goal is pretty simple. I want to expose a stored procedure, called GetContacts, in my AdventureWorks Database as an XML Web Service. As luck would have it, this is an extremely easy thing to do in SQL Server 2005 without requiring IIS.
CREATE PROCEDURE [dbo].[GetContacts]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 20 [FirstName],[LastName] FROM
[Person].[Contact] ORDER BY [LastName];
END
Create HTTP Endpoint
Fire up SQL Server Management Studio, choose the AdventureWorks Database, and open a New Query Window. Create an HTTP Endpoint with the following statement:
CREATE ENDPOINT AW_Contacts
STATE = Started
AS HTTP
(
PATH = '/Contacts',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'GetContacts'
(NAME = 'AdventureWorks.dbo.GetContacts'),
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = DEFAULT
)
If you get an error executing this query that mentions another service using the port, make sure you turn off IIS.
A Path = '/Contacts', Site = '*', and Ports = (CLEAR) means that your URL will be to the default host, localhost, not require SSL, and look like the following:
http://localhost/contacts?WSDL
The FOR SOAP part of the command relates the GetContacts Method to the GetContacts Stored Procedure. Once you execute the command, take a peek at the Server Objects to see your HTTP Endpoint:
Fire Up Visual Studio 2005 to Consume the XML Web Service
At this point, you consume the XML Web Service like every other web service. Create a Windows Application, called Native Http, drop a DataGridView on the form, and add a Web Reference specifying the following URL:
http://localhost/contacts?WSDL
Leave the web reference name as localhost just so you can add the following code to the Form Load Event:
localhost.AW_Contacts contacts =
new Native_Http.localhost.AW_Contacts();
contacts.Credentials = CredentialCache.DefaultCredentials;
dataGridView1.DataSource =
(contacts.GetContacts()[0] as DataSet).Tables[0];
Run the application and magically see the contacts appear in the DataGridView.
Conclusion
Native XML Web Services in SQL Server 2005 is a pretty interesting feature thats starts to blur the lines of how you look at web services. Most of the time I think of XML Web Services in terms of IIS and now one can easily expose a stored procedure as an XML Web Service by creating a simple HTTP Endpoint in SQL Server 2005 using SQL Server Management Studio.
Source: David Hayden ( .NET Developer )
SQL Server 2005 Tutorials and Information