Welcome!

Microsoft Cloud Authors: David H Deans, Yeshim Deniz, Janakiram MSV, Andreas Grabner, Stackify Blog

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
From 2013, NTT Communications has been providing cPaaS service, SkyWay. Its customer’s expectations for leveraging WebRTC technology are not only typical real-time communication use cases such as Web conference, remote education, but also IoT use cases such as remote camera monitoring, smart-glass, and robotic. Because of this, NTT Communications has numerous IoT business use-cases that its customers are developing on top of PaaS. WebRTC will lead IoT businesses to be more innovative and address...
When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...
SYS-CON Events announced today that App2Cloud will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. App2Cloud is an online Platform, specializing in migrating legacy applications to any Cloud Providers (AWS, Azure, Google Cloud).
IoT is at the core or many Digital Transformation initiatives with the goal of re-inventing a company's business model. We all agree that collecting relevant IoT data will result in massive amounts of data needing to be stored. However, with the rapid development of IoT devices and ongoing business model transformation, we are not able to predict the volume and growth of IoT data. And with the lack of IoT history, traditional methods of IT and infrastructure planning based on the past do not app...
Internet-of-Things discussions can end up either going down the consumer gadget rabbit hole or focused on the sort of data logging that industrial manufacturers have been doing forever. However, in fact, companies today are already using IoT data both to optimize their operational technology and to improve the experience of customer interactions in novel ways. In his session at @ThingsExpo, Gordon Haff, Red Hat Technology Evangelist, shared examples from a wide range of industries – including en...
To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. Jack Norris reviews best practices to show how companies develop, deploy, and dynamically update these applications and how this data-first...
Intelligent Automation is now one of the key business imperatives for CIOs and CISOs impacting all areas of business today. In his session at 21st Cloud Expo, Brian Boeggeman, VP Alliances & Partnerships at Ayehu, will talk about how business value is created and delivered through intelligent automation to today’s enterprises. The open ecosystem platform approach toward Intelligent Automation that Ayehu delivers to the market is core to enabling the creation of the self-driving enterprise.
"We're a cybersecurity firm that specializes in engineering security solutions both at the software and hardware level. Security cannot be an after-the-fact afterthought, which is what it's become," stated Richard Blech, Chief Executive Officer at Secure Channels, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
Consumers increasingly expect their electronic "things" to be connected to smart phones, tablets and the Internet. When that thing happens to be a medical device, the risks and benefits of connectivity must be carefully weighed. Once the decision is made that connecting the device is beneficial, medical device manufacturers must design their products to maintain patient safety and prevent compromised personal health information in the face of cybersecurity threats. In his session at @ThingsExpo...
Detecting internal user threats in the Big Data eco-system is challenging and cumbersome. Many organizations monitor internal usage of the Big Data eco-system using a set of alerts. This is not a scalable process given the increase in the number of alerts with the accelerating growth in data volume and user base. Organizations are increasingly leveraging machine learning to monitor only those data elements that are sensitive and critical, autonomously establish monitoring policies, and to detect...
SYS-CON Events announced today that Grape Up will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
SYS-CON Events announced today that Massive Networks will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Massive Networks mission is simple. To help your business operate seamlessly with fast, reliable, and secure internet and network solutions. Improve your customer's experience with outstanding connections to your cloud.
Everything run by electricity will eventually be connected to the Internet. Get ahead of the Internet of Things revolution and join Akvelon expert and IoT industry leader, Sergey Grebnov, in his session at @ThingsExpo, for an educational dive into the world of managing your home, workplace and all the devices they contain with the power of machine-based AI and intelligent Bot services for a completely streamlined experience.
Because IoT devices are deployed in mission-critical environments more than ever before, it’s increasingly imperative they be truly smart. IoT sensors simply stockpiling data isn’t useful. IoT must be artificially and naturally intelligent in order to provide more value In his session at @ThingsExpo, John Crupi, Vice President and Engineering System Architect at Greenwave Systems, will discuss how IoT artificial intelligence (AI) can be carried out via edge analytics and machine learning techn...
SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
In the enterprise today, connected IoT devices are everywhere – both inside and outside corporate environments. The need to identify, manage, control and secure a quickly growing web of connections and outside devices is making the already challenging task of security even more important, and onerous. In his session at @ThingsExpo, Rich Boyer, CISO and Chief Architect for Security at NTT i3, discussed new ways of thinking and the approaches needed to address the emerging challenges of security i...
SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...
SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
In his opening keynote at 20th Cloud Expo, Michael Maximilien, Research Scientist, Architect, and Engineer at IBM, discussed the full potential of the cloud and social data requires artificial intelligence. By mixing Cloud Foundry and the rich set of Watson services, IBM's Bluemix is the best cloud operating system for enterprises today, providing rapid development and deployment of applications that can take advantage of the rich catalog of Watson services to help drive insights from the vast t...
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st 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 devic...