Click here to close now.




















Welcome!

Microsoft Cloud Authors: Eric Aarrestad, Greg O'Connor, Liz McMillan, Aleksei Gavrilenko, Elizabeth White

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
SYS-CON Events announced today that HPM Networks will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. For 20 years, HPM Networks has been integrating technology solutions that solve complex business challenges. HPM Networks has designed solutions for both SMB and enterprise customers throughout the San Francisco Bay Area.
For IoT to grow as quickly as analyst firms’ project, a lot is going to fall on developers to quickly bring applications to market. But the lack of a standard development platform threatens to slow growth and make application development more time consuming and costly, much like we’ve seen in the mobile space. In his session at @ThingsExpo, Mike Weiner, Product Manager of the Omega DevCloud with KORE Telematics Inc., discussed the evolving requirements for developers as IoT matures and conducted a live demonstration of how quickly application development can happen when the need to comply wit...
The Internet of Everything (IoE) brings together people, process, data and things to make networked connections more relevant and valuable than ever before – transforming information into knowledge and knowledge into wisdom. IoE creates new capabilities, richer experiences, and unprecedented opportunities to improve business and government operations, decision making and mission support capabilities.
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Architect for the Internet of Things and Intelligent Systems, described how to revolutionize your archit...
MuleSoft has announced the findings of its 2015 Connectivity Benchmark Report on the adoption and business impact of APIs. The findings suggest traditional businesses are quickly evolving into "composable enterprises" built out of hundreds of connected software services, applications and devices. Most are embracing the Internet of Things (IoT) and microservices technologies like Docker. A majority are integrating wearables, like smart watches, and more than half plan to generate revenue with APIs within the next year.
Growth hacking is common for startups to make unheard-of progress in building their business. Career Hacks can help Geek Girls and those who support them (yes, that's you too, Dad!) to excel in this typically male-dominated world. Get ready to learn the facts: Is there a bias against women in the tech / developer communities? Why are women 50% of the workforce, but hold only 24% of the STEM or IT positions? Some beginnings of what to do about it! In her Opening Keynote at 16th Cloud Expo, Sandy Carter, IBM General Manager Cloud Ecosystem and Developers, and a Social Business Evangelist, d...
In his keynote at 16th Cloud Expo, Rodney Rogers, CEO of Virtustream, discussed the evolution of the company from inception to its recent acquisition by EMC – including personal insights, lessons learned (and some WTF moments) along the way. Learn how Virtustream’s unique approach of combining the economics and elasticity of the consumer cloud model with proper performance, application automation and security into a platform became a breakout success with enterprise customers and a natural fit for the EMC Federation.
The Internet of Things is not only adding billions of sensors and billions of terabytes to the Internet. It is also forcing a fundamental change in the way we envision Information Technology. For the first time, more data is being created by devices at the edge of the Internet rather than from centralized systems. What does this mean for today's IT professional? In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists addressed this very serious issue of profound change in the industry.
Discussions about cloud computing are evolving into discussions about enterprise IT in general. As enterprises increasingly migrate toward their own unique clouds, new issues such as the use of containers and microservices emerge to keep things interesting. In this Power Panel at 16th Cloud Expo, moderated by Conference Chair Roger Strukhoff, panelists addressed the state of cloud computing today, and what enterprise IT professionals need to know about how the latest topics and trends affect their organization.
It is one thing to build single industrial IoT applications, but what will it take to build the Smart Cities and truly society-changing applications of the future? The technology won’t be the problem, it will be the number of parties that need to work together and be aligned in their motivation to succeed. In his session at @ThingsExpo, Jason Mondanaro, Director, Product Management at Metanga, discussed how you can plan to cooperate, partner, and form lasting all-star teams to change the world and it starts with business models and monetization strategies.
Converging digital disruptions is creating a major sea change - Cisco calls this the Internet of Everything (IoE). IoE is the network connection of People, Process, Data and Things, fueled by Cloud, Mobile, Social, Analytics and Security, and it represents a $19Trillion value-at-stake over the next 10 years. In her keynote at @ThingsExpo, Manjula Talreja, VP of Cisco Consulting Services, discussed IoE and the enormous opportunities it provides to public and private firms alike. She will share what businesses must do to thrive in the IoE economy, citing examples from several industry sectors.
There will be 150 billion connected devices by 2020. New digital businesses have already disrupted value chains across every industry. APIs are at the center of the digital business. You need to understand what assets you have that can be exposed digitally, what their digital value chain is, and how to create an effective business model around that value chain to compete in this economy. No enterprise can be complacent and not engage in the digital economy. Learn how to be the disruptor and not the disruptee.
Akana has released Envision, an enhanced API analytics platform that helps enterprises mine critical insights across their digital eco-systems, understand their customers and partners and offer value-added personalized services. “In today’s digital economy, data-driven insights are proving to be a key differentiator for businesses. Understanding the data that is being tunneled through their APIs and how it can be used to optimize their business and operations is of paramount importance,” said Alistair Farquharson, CTO of Akana.
Business as usual for IT is evolving into a "Make or Buy" decision on a service-by-service conversation with input from the LOBs. How does your organization move forward with cloud? In his general session at 16th Cloud Expo, Paul Maravei, Regional Sales Manager, Hybrid Cloud and Managed Services at Cisco, discusses how Cisco and its partners offer a market-leading portfolio and ecosystem of cloud infrastructure and application services that allow you to uniquely and securely combine cloud business applications and services across multiple cloud delivery models.
The enterprise market will drive IoT device adoption over the next five years. In his session at @ThingsExpo, John Greenough, an analyst at BI Intelligence, division of Business Insider, analyzed how companies will adopt IoT products and the associated cost of adopting those products. John Greenough is the lead analyst covering the Internet of Things for BI Intelligence- Business Insider’s paid research service. Numerous IoT companies have cited his analysis of the IoT. Prior to joining BI Intelligence, he worked analyzing bank technology for Corporate Insight and The Clearing House Payment...
"Optimal Design is a technology integration and product development firm that specializes in connecting devices to the cloud," stated Joe Wascow, Co-Founder & CMO of Optimal Design, in this SYS-CON.tv interview at @ThingsExpo, held June 9-11, 2015, at the Javits Center in New York City.
SYS-CON Events announced today that CommVault has been named “Bronze Sponsor” of SYS-CON's 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. A singular vision – a belief in a better way to address current and future data management needs – guides CommVault in the development of Singular Information Management® solutions for high-performance data protection, universal availability and simplified management of data on complex storage networks. CommVault's exclusive single-platform architecture gives companies unp...
Electric Cloud and Arynga have announced a product integration partnership that will bring Continuous Delivery solutions to the automotive Internet-of-Things (IoT) market. The joint solution will help automotive manufacturers, OEMs and system integrators adopt DevOps automation and Continuous Delivery practices that reduce software build and release cycle times within the complex and specific parameters of embedded and IoT software systems.
"ciqada is a combined platform of hardware modules and server products that lets people take their existing devices or new devices and lets them be accessible over the Internet for their users," noted Geoff Engelstein of ciqada, a division of Mars International, in this SYS-CON.tv interview at @ThingsExpo, held June 9-11, 2015, at the Javits Center in New York City.
Internet of Things is moving from being a hype to a reality. Experts estimate that internet connected cars will grow to 152 million, while over 100 million internet connected wireless light bulbs and lamps will be operational by 2020. These and many other intriguing statistics highlight the importance of Internet powered devices and how market penetration is going to multiply many times over in the next few years.