Welcome!

Microsoft Cloud Authors: Janakiram MSV, Pat Romanski, Steven Mandel, John Basso, Liz McMillan

Related Topics: Microsoft Cloud

Microsoft Cloud: 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
Pulzze Systems was happy to participate in such a premier event and thankful to be receiving the winning investment and global network support from G-Startup Worldwide. It is an exciting time for Pulzze to showcase the effectiveness of innovative technologies and enable them to make the world smarter and better. The reputable contest is held to identify promising startups around the globe that are assured to change the world through their innovative products and disruptive technologies. There w...
DevOps at Cloud Expo, taking place Nov 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. 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 dev...
Today we can collect lots and lots of performance data. We build beautiful dashboards and even have fancy query languages to access and transform the data. Still performance data is a secret language only a couple of people understand. The more business becomes digital the more stakeholders are interested in this data including how it relates to business. Some of these people have never used a monitoring tool before. They have a question on their mind like “How is my application doing” but no id...
Personalization has long been the holy grail of marketing. Simply stated, communicate the most relevant offer to the right person and you will increase sales. To achieve this, you must understand the individual. Consequently, digital marketers developed many ways to gather and leverage customer information to deliver targeted experiences. In his session at @ThingsExpo, Lou Casal, Founder and Principal Consultant at Practicala, discussed how the Internet of Things (IoT) has accelerated our abil...
With so much going on in this space you could be forgiven for thinking you were always working with yesterday’s technologies. So much change, so quickly. What do you do if you have to build a solution from the ground up that is expected to live in the field for at least 5-10 years? This is the challenge we faced when we looked to refresh our existing 10-year-old custom hardware stack to measure the fullness of trash cans and compactors.
The emerging Internet of Everything creates tremendous new opportunities for customer engagement and business model innovation. However, enterprises must overcome a number of critical challenges to bring these new solutions to market. In his session at @ThingsExpo, Michael Martin, CTO/CIO at nfrastructure, outlined these key challenges and recommended approaches for overcoming them to achieve speed and agility in the design, development and implementation of Internet of Everything solutions wi...
Cloud computing is being adopted in one form or another by 94% of enterprises today. Tens of billions of new devices are being connected to The Internet of Things. And Big Data is driving this bus. An exponential increase is expected in the amount of information being processed, managed, analyzed, and acted upon by enterprise IT. This amazing is not part of some distant future - it is happening today. One report shows a 650% increase in enterprise data by 2020. Other estimates are even higher....
SYS-CON Events announced today that 910Telecom will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and ...
SYS-CON Events announced today Telecom Reseller has been named “Media Sponsor” of SYS-CON's 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
Smart Cities are here to stay, but for their promise to be delivered, the data they produce must not be put in new siloes. In his session at @ThingsExpo, Mathias Herberts, Co-founder and CTO of Cityzen Data, will deep dive into best practices that will ensure a successful smart city journey.
The 19th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Digital Transformation, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportuni...
There is growing need for data-driven applications and the need for digital platforms to build these apps. In his session at 19th Cloud Expo, Muddu Sudhakar, VP and GM of Security & IoT at Splunk, will cover different PaaS solutions and Big Data platforms that are available to build applications. In addition, AI and machine learning are creating new requirements that developers need in the building of next-gen apps. The next-generation digital platforms have some of the past platform needs a...
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devices - comp...
Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, will discuss the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
19th Cloud Expo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterpri...
I wanted to gather all of my Internet of Things (IOT) blogs into a single blog (that I could later use with my University of San Francisco (USF) Big Data “MBA” course). However as I started to pull these blogs together, I realized that my IOT discussion lacked a vision; it lacked an end point towards which an organization could drive their IOT envisioning, proof of value, app dev, data engineering and data science efforts. And I think that the IOT end point is really quite simple…
Identity is in everything and customers are looking to their providers to ensure the security of their identities, transactions and data. With the increased reliance on cloud-based services, service providers must build security and trust into their offerings, adding value to customers and improving the user experience. Making identity, security and privacy easy for customers provides a unique advantage over the competition.
Is the ongoing quest for agility in the data center forcing you to evaluate how to be a part of infrastructure automation efforts? As organizations evolve toward bimodal IT operations, they are embracing new service delivery models and leveraging virtualization to increase infrastructure agility. Therefore, the network must evolve in parallel to become equally agile. Read this essential piece of Gartner research for recommendations on achieving greater agility.
SYS-CON Events announced today that Venafi, the Immune System for the Internet™ and the leading provider of Next Generation Trust Protection, will exhibit at @DevOpsSummit at 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Venafi is the Immune System for the Internet™ that protects the foundation of all cybersecurity – cryptographic keys and digital certificates – so they can’t be misused by bad guys in attacks...
For basic one-to-one voice or video calling solutions, WebRTC has proven to be a very powerful technology. Although WebRTC’s core functionality is to provide secure, real-time p2p media streaming, leveraging native platform features and server-side components brings up new communication capabilities for web and native mobile applications, allowing for advanced multi-user use cases such as video broadcasting, conferencing, and media recording.