Welcome!

.NET Authors: Elizabeth White, Jayaram Krishnaswamy, Sematext Blog, ITinvolve Blog, Aditya Banerjee

Related Topics: .NET

.NET: Article

SQL Server Web Services

Create and host XML Web services inside SQL Server 2005

These days, applications built around a service-oriented architecture (SOA) are all the rage. Complex systems can be written as a collection of services that communicate with each other through standard protocols. When implemented properly, these systems can become immensely flexible, scalable, and easy to maintain. The most common SOA designs utilize Web services as the communications medium, because they can be created and consumed by disparate systems and platforms. This highly useful capability has made Web services a very important part of today's information systems.

Up until now, the classic Microsoft implementation of a Web service involved a back-end database server running SQL Server 2000 and a front-end Web server running a .NET Web service application on IIS. Now that SQL Server 2005 is here, we have an additional option: we can create and host Web services using SQL Server alone. In this scenario, no Web server is needed. When running on Windows Server 2003 or Windows XP with Service Pack 2, IIS is not needed either. These operating systems provide the HTTP API, also known as HTTP.SYS, which SQL Server uses to provide native Web service functionality.

In this article I will show how to create a Web service inside SQL Server. I will show how to define the service and how to access it from a .NET client application. Along the way, we will see how incredibly flexible SQL Server's Web service support is. Hopefully, we will also gain an appreciation of how capable a tool SQL Server 2005 has become. Let's get started.

Endpoints
SQL Server 2005 provides Web services through endpoints. Endpoints define the properties of a connection, which can use either the HTTP or the TCP protocol. They can provide support for SOAP, Service Broker, and Database Mirroring. As you might guess, the syntax for creating a new endpoint is quite complex. Because of this, I am going to focus solely on the syntax required to create a Web service. Listing 1 shows an example of this. We'll walk through each line in detail.

Endpoints are created via the CREATE ENDPOINT statement. This statement consists of four main parts: endpoint name, the initial state, the AS clause, and the FOR clause.

Name and Initial State
The first two parts are easy: they provide a name for the endpoint and specify the state of the endpoint after it is created. The state can be STARTED, STOPPED, or DISABLED. STARTED indicates that the endpoint is actively listening for connections and will respond to connection attempts. STOPPED indicates that the endpoint will listen for connections and will respond to connection attempts, but will refuse the connection and return an error to the client. The last option, DISABLED, indicates that the endpoint will not listen for connections, nor will it respond to connection attempts. STOPPED is the default, but this can be changed either through the CREATE ENDPOINT statement or via an ALTER ENDPOINT statement at a later time.

AS Clause
The AS clause specifies the communications protocol that the endpoint will use. Here we have specified the HTTP protocol, which is the protocol required for a Web service. (TCP is an option for other types of services, and would have different settings than those described here.) The exact implementation details are controlled by a list of specific options included inside parentheses. Those used in our example are described here:

  • PATH specifies the URL of the endpoint. This setting, along with the SITE setting described later, fully specifies the URL that will be used to invoke the Web service. (For example, I've used '/Employee,' so the URL would be HTTP://ServerName/Employee.)
  • AUTHENTICATION specifies the type(s) of authentication that will be used. This is a comma-separated list of authentication methods, including those that we should all be familiar with: BASIC, DIGEST, NTLM, KERBEROS, and INTEGRATED. These work as expected, although additional in-depth information can be found in SQL Server Books Online. My example used Windows Integrated authentication only, so I've specified INTEGRATED.
  • PORTS specifies the type of port to use: CLEAR and/or SSL. CLEAR indicates a non-encrypted port, which requires clients to use the HTTP protocol. SSL indicates an encrypted port, requiring clients to use the HTTPS protocol. If required, both settings can be used simultaneously. The example uses unencrypted HTTP communications, so the appropriate setting is CLEAR.
  • CLEAR_PORT specifies the actual port that will be used by unencrypted communications. If not specified, port 80 is assumed. In this example, I've used port 8080 because my computer has IIS installed, which means that port 80 is already in use. There is also an SSL_PORT setting that we can use if needed, and it defaults to port 443. My example doesn't use SSL, so I haven't included this option.
  • SITE determines the ServerName portion of the Web service URL. If we use an asterisk ("*"), it instructs the endpoint to use all possible host names that aren't explicitly reserved. I used this setting in my example so that both HTTP://localhost/Employee and HTTP://jdixon/Employee would work. If we use a plus sign ("+"), then the endpoint will use all possible host names, even if they are reserved. Finally, we could explicitly specify the host name, which would ensure that only that name would be used.
  • COMPRESSION specifies whether client requests for compressed communications will be honored. If enabled, GZIP encoding will be used when client requests include the appropriate HTTP encoding header. Disabled is the default, and specifies that compression will not be used even if the client properly requests it. I've chosen to enable compression in the example, although I don't specifically need it.
Our example AS clause is now complete. There are other settings that can be used, especially when different authentication methods are chosen, but those are beyond the scope of this article. Instead, we need to move on and set up our service options.

FOR Clause
The FOR clause specifies the service that the endpoint provides. This can be SOAP, SERVICE_BROKER, or DATABASE_MIRRORING. We want to create a Web service, so we must choose SOAP. (Once again, the possible settings vary greatly depending upon which option we choose, so I'm going to restrict my explanation to SOAP-specific settings. Consult Books Online for more details.) The FOR clause consists of one or more WEBMETHOD clauses followed by some service-wide settings.

The WEBMETHOD clause does exactly what you would expect; it defines a single Web method. If a Web service needs multiple methods, then multiple WEBMETHOD clauses will be needed. In our example, there are two methods. The two methods are quite similar; therefore, I'll describe just the first one.

The first part of the WEBMETHOD clause defines the name, or alias, of the method. The alias can also include a namespace, but this is optional. If the namespace is not included, then the namespace from the endpoint is used instead. (The endpoint's namespace will be described in a moment.) After the alias, additional settings are used to specify exactly how this method will function. Note that these settings are included inside parentheses, and that the parentheses are required.

  • NAME specifies the fully-qualified name of the stored procedure or user-defined function that will be invoked by the method. The name must be in the standard database.owner.name format. My example uses a stored procedure named uspGetEmployeeManagers that is found in the AdventureWorks database, within the dbo schema.
  • SCHEMA determines whether the SOAP message for this method will include an inline XSD schema. There are three options; NONE, STANDARD, and DEFAULT. NONE specifies that no schema will be used, while STANDARD specifies that one will. DEFAULT specifies that the setting from the endpoint itself should be used. If no value is specified, DEFAULT will be assumed.
  • FORMAT determines the exact content of the method response. The options are ALL_RESULTS, ROWSETS_ONLY, and NONE. ALL_RESULTS instructs the method to return not only the output from the stored procedure or user-defined function, but also the row count and any error messages or warnings that may exist. ROWSETS_ONLY omits everything but the actual results. NONE suppresses the results too, making this option useful only in very specific circumstances (see SQL Books Online for details). The example uses ROWSETS_ONLY.
In the example, the second WEB-METHOD clause is almost identical to the first, so we'll move on to the service-wide settings.

The final four settings apply to the whole Web service, and not to a specific method:

  • BATCHES specifies whether ad hoc T-SQL statements can be executed via this service. DISABLED, the default, prevents this. ENABLED allows it. When enabled, a special method called SQLBATCH will be created that supports ad hoc queries.
  • WSDL determines the type of WSDL (Web Services Definition Language) document that the Web service will generate. A value of NONE prevents the WSDL from being generated. DEFAULT, on the other hand, directs that the WSDL will be generated. In those rare cases where a special WSDL document is needed, you can specify the name of a stored procedure that returns the special document.
  • DATABASE identifies the name of the database context against which the Web methods will be executed. Our service uses the AdventureWorks database, so that name is included here. If DEFAULT is specified instead, then the default database for the login will be used.
  • NAMESPACE specifies the namespace for the endpoint. If DEFAULT is used, or if no namespace is specified, then the namespace will be HTTP://tempuri.org. If a namespace is included at the WEBMETHOD level, then that namespace will override the one provided here.

More Stories By Jerry Dixon

Jerry Dixon is a senior developer and architect for ACH Food Companies in Memphis, Tennessee. Over the past 16 years he has led development projects for a number of enterprise, mid-level, and small business organizations. While he has fulfilled multiple roles as an infrastructure designer, database administrator, and software developer, he specializes in XML, SQL and ASP.NET. He is a co-leader and frequent presenter at the Memphis .NET User Group. Jerry holds the following Microsoft certifications: MCSD (VB 6.0 and .NET), MCDBA (SQL 2000), MCSA (Windows 2000 and 2003), MCSE (Windows 2000), MCAD (.NET), MCT. He resides in Olive Branch, MS with his wife and son.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect at GE, and Ibrahim Gokcen, who leads GE's advanced IoT analytics, focused on the Internet of Things / Industrial Internet and how to make it operational for business end-users. Learn about the challenges posed by machine and sensor data and how to marry it with enterprise data. They also discussed the tips and tricks to provide the Industrial Internet as an end-user consumable service using Big Data Analytics and Industrial Cloud.
Things are being built upon cloud foundations to transform organizations. This CEO Power Panel at 15th Cloud Expo, moderated by Roger Strukhoff, Cloud Expo and @ThingsExpo conference chair, addressed the big issues involving these technologies and, more important, the results they will achieve. Rodney Rogers, chairman and CEO of Virtustream; Brendan O'Brien, co-founder of Aria Systems, Bart Copeland, president and CEO of ActiveState Software; Jim Cowie, chief scientist at Dyn; Dave Wagstaff, VP and chief architect at BSQUARE Corporation; Seth Proctor, CTO of NuoDB, Inc.; and Andris Gailitis, C...
How do APIs and IoT relate? The answer is not as simple as merely adding an API on top of a dumb device, but rather about understanding the architectural patterns for implementing an IoT fabric. There are typically two or three trends: Exposing the device to a management framework Exposing that management framework to a business centric logic Exposing that business layer and data to end users. This last trend is the IoT stack, which involves a new shift in the separation of what stuff happens, where data lives and where the interface lies. For instance, it's a mix of architectural styles ...
The Industrial Internet revolution is now underway, enabled by connected machines and billions of devices that communicate and collaborate. The massive amounts of Big Data requiring real-time analysis is flooding legacy IT systems and giving way to cloud environments that can handle the unpredictable workloads. Yet many barriers remain until we can fully realize the opportunities and benefits from the convergence of machines and devices with Big Data and the cloud, including interoperability, data security and privacy.
Technology is enabling a new approach to collecting and using data. This approach, commonly referred to as the "Internet of Things" (IoT), enables businesses to use real-time data from all sorts of things including machines, devices and sensors to make better decisions, improve customer service, and lower the risk in the creation of new revenue opportunities. In his General Session at Internet of @ThingsExpo, Dave Wagstaff, Vice President and Chief Architect at BSQUARE Corporation, discuss the real benefits to focus on, how to understand the requirements of a successful solution, the flow of ...
Cloud Expo 2014 TV commercials will feature @ThingsExpo, which was launched in June, 2014 at New York City's Javits Center as the largest 'Internet of Things' event in the world.
"People are a lot more knowledgeable about APIs now. There are two types of people who work with APIs - IT people who want to use APIs for something internal and the product managers who want to do something outside APIs for people to connect to them," explained Roberto Medrano, Executive Vice President at SOA Software, in this SYS-CON.tv interview at Cloud Expo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Performance is the intersection of power, agility, control, and choice. If you value performance, and more specifically consistent performance, you need to look beyond simple virtualized compute. Many factors need to be considered to create a truly performant environment. In his General Session at 15th Cloud Expo, Harold Hannon, Sr. Software Architect at SoftLayer, discussed how to take advantage of a multitude of compute options and platform features to make cloud the cornerstone of your online presence.
In this Women in Technology Power Panel at 15th Cloud Expo, moderated by Anne Plese, Senior Consultant, Cloud Product Marketing at Verizon Enterprise, Esmeralda Swartz, CMO at MetraTech; Evelyn de Souza, Data Privacy and Compliance Strategy Leader at Cisco Systems; Seema Jethani, Director of Product Management at Basho Technologies; Victoria Livschitz, CEO of Qubell Inc.; Anne Hungate, Senior Director of Software Quality at DIRECTV, discussed what path they took to find their spot within the technology industry and how do they see opportunities for other women in their area of expertise.
Wearable devices have come of age. The primary applications of wearables so far have been "the Quantified Self" or the tracking of one's fitness and health status. We propose the evolution of wearables into social and emotional communication devices. Our BE(tm) sensor uses light to visualize the skin conductance response. Our sensors are very inexpensive and can be massively distributed to audiences or groups of any size, in order to gauge reactions to performances, video, or any kind of presentation. In her session at @ThingsExpo, Jocelyn Scheirer, CEO & Founder of Bionolux, will discuss ho...
DevOps Summit 2015 New York, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that it is now accepting Keynote Proposals. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long development cycles that produce software that is obsolete at launch. DevOps may be disruptive, but it is essential.
Almost everyone sees the potential of Internet of Things but how can businesses truly unlock that potential. The key will be in the ability to discover business insight in the midst of an ocean of Big Data generated from billions of embedded devices via Systems of Discover. Businesses will also need to ensure that they can sustain that insight by leveraging the cloud for global reach, scale and elasticity.
“With easy-to-use SDKs for Atmel’s platforms, IoT developers can now reap the benefits of realtime communication, and bypass the security pitfalls and configuration complexities that put IoT deployments at risk,” said Todd Greene, founder & CEO of PubNub. PubNub will team with Atmel at CES 2015 to launch full SDK support for Atmel’s MCU, MPU, and Wireless SoC platforms. Atmel developers now have access to PubNub’s secure Publish/Subscribe messaging with guaranteed ¼ second latencies across PubNub’s 14 global points-of-presence. PubNub delivers secure communication through firewalls, proxy ser...
We’re no longer looking to the future for the IoT wave. It’s no longer a distant dream but a reality that has arrived. It’s now time to make sure the industry is in alignment to meet the IoT growing pains – cooperate and collaborate as well as innovate. In his session at @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, will examine the key ingredients to IoT success and identify solutions to challenges the industry is facing. The deep industry expertise behind this presentation will provide attendees with a leading edge view of rapidly emerging IoT oppor...
Connected devices and the Internet of Things are getting significant momentum in 2014. In his session at Internet of @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, examined three key elements that together will drive mass adoption of the IoT before the end of 2015. The first element is the recent advent of robust open source protocols (like AllJoyn and WebRTC) that facilitate M2M communication. The second is broad availability of flexible, cost-effective storage designed to handle the massive surge in back-end data in a world where timely analytics is e...
The 3rd International Internet of @ThingsExpo, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that its Call for Papers is now open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
"There is a natural synchronization between the business models, the IoT is there to support ,” explained Brendan O'Brien, Co-founder and Chief Architect of Aria Systems, in this SYS-CON.tv interview at the 15th International Cloud Expo®, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges. In his session at @ThingsExpo, Jeff Kaplan, Managing Director of THINKstrategies, will examine why IT must finally fulfill its role in support of its SBUs or face a new round of...
The BPM world is going through some evolution or changes where traditional business process management solutions really have nowhere to go in terms of development of the road map. In this demo at 15th Cloud Expo, Kyle Hansen, Director of Professional Services at AgilePoint, shows AgilePoint’s unique approach to dealing with this market circumstance by developing a rapid application composition or development framework.
The Internet of Things will greatly expand the opportunities for data collection and new business models driven off of that data. In her session at @ThingsExpo, Esmeralda Swartz, CMO of MetraTech, discussed how for this to be effective you not only need to have infrastructure and operational models capable of utilizing this new phenomenon, but increasingly service providers will need to convince a skeptical public to participate. Get ready to show them the money!