SQL Server 2005 Native XML Web Services : Exposing SQL Programmability as Web Services (part 1)

3/23/2013 4:04:52 AM

Native XML Web Services provides two types of Web services access to data: exposing specific stored procedures or user-defined functions (UDFs) as Web methods, and batch mode access.

1. Stored Procedures and User-Defined Functions

Native XML Web Services allows virtually any stored procedure or UDF to be exposed as a Web method. There are a few minor limitations, such as restrictions on stored procedure parameter names and lack of support for UDFs that return tables. However, in general you can choose to expose any existing stored procedure as a Web service, allowing it to do double duty as a normal stored procedure in your applications and as a Web service.

2. SQL Batch

In addition to exposing individual stored procedures and UDFs, Native XML Web Services also supports exposing T-SQL batch statements. Because of the wide-ranging power of this level of access, SQL batch is disabled by default.

3. Reserving URLs with Http.sys

It is not strictly necessary to register a URL with Http.sys in order to expose an endpoint. The CREATE ENDPOINT statement implicitly registers the URL defined by its PORTS, SITE, and PATH arguments. However, this registration takes place only when the endpoint is running, so it is possible for another application to register the same URL before your service starts up, causing a conflict. Therefore, it is usually a good idea to reserve the specific URL for your endpoints.

To register a URL to be handled by SQL Server, you call the sp_reserve_http_namespace stored procedure:

EXEC sp_reserve_http_namespace N'http://localhost:80/sql'


EXEC sp_reserve_http_namespace N'https://localhost:443/sql'

The registration of namespaces with Http.sys is hierarchical, which means any sub URLs, such as http://localhost:80/sql/AdventureSales, are also handled by SQL Server.


You must be logged on to SQL Server with an account that has Windows Administrator privileges on the computer where the instance of SQL Server 2005 is installed in order to reserve an HTTP namespace with the kernel-mode HTTP driver (Http.sys).

4. Creating and Managing Endpoints

For obvious security reasons, Native XML Web Services does not expose any Web services by default—you must explicitly expose them by creating endpoints. Only system administrators can create endpoints or grant rights to other users to create them. However, if you have the correct permissions, creating endpoints is easy—you simply execute the CREATE ENDPOINT statement.


The CREATE ENDPOINT statement has a lot of options, but here we’ll simplify it and look only at the most commonly used options for creating Web service endpoints.

PORTS = ( { CLEAR | SSL} [ ,... n ] ),
SITE = {'*' | '+' | 'webSite' },
PATH = 'url',
[ WEBMETHOD [ 'namespace' .] 'method_alias'
   NAME = '',
WSDL = { NONE | DEFAULT | 'sp_name' },
DATABASE = { 'database_name' | DEFAULT },
NAMESPACE = { 'namespace' | DEFAULT },



The STATE argument specifies the state of the endpoint after it is created. By default, an endpoint is created in the STOPPED state and you must then use the ALTER ENDPOINT statement to activate it. If you want the endpoint to be activated immediately upon creation, you specify STARTED for this argument. When an endpoint is in the STOPPED state, the server listens for and handles requests to the port but returns an error to clients indicating that the port is not active. When an endpoint is in the DISABLED state, the server does not listen on the port and will not respond to any client requests.


You specify the full URL address for the endpoint in three parts: [protocol]://[host name] + [path], defined by the PORTS, SITE, and PATH arguments, respectively. The PORTS argument determines whether the URL begins with http:// (for CLEAR) or https:// (for SSL). Note that it is possible to listen on both ports. The SITE argument defines the host name used. The PATH argument defines the rest of the URL after the server name. For example, you would use the following options to define the URL as http://localhost/sql/AdventureSales.

SITE = 'localhost'
PATH = '/sql/AdventureSales'

The following options result in the URL https://myserver/NXWSEndpoints/MyEndpoint.

PORTS = ( SSL ),
SITE = 'MyServer'
PATH = '/NXWSEndpoints/MyEndpoint'


Although the use of sql as the base of the PATH argument seems to be common usage, this is by no means required. You can put any value (that results in a valid URL string) in the PATH argument to define the URL.


The AUTHENTICATION argument defines the types of authentication supported by the endpoint. These options should be familiar to you because they are the common options available under IIS for authenticating Web sites and Web services. However, conspicuously missing from the list is an option for anonymous access. This is by design; the caller must be authenticated as a Windows account or an SQL Server account to invoke a Web service on an endpoint.

You should pay attention to a few details regarding the authentication options. Because Basic authentication passes the username and password in clear text, you are required to choose the SSL value for PORTS when you specify Basic authentication. If the INTEGRATED value is specified, the server will use Kerberos or NTLM authentication, depending on how the client initiates authentication. Before you can use Kerberos authentication, the SQL Server must associate a Service Principal Name (SPN) with the account it will be running on. You can do this using the Windows SetSPN.exe support tool in Windows 2003.


You must enter a WEBMETHOD clause for each stored procedure or UDF that you want to expose. The method_alias argument allows you to assign the public name for the Web method, which can differ from the actual name of the stored procedure or function it maps to. The optional namespace argument allows you to override, for this particular Web method, the namespace defined at the endpoint level. You set NAME to the actual database name for the stored procedure or function, fully qualified as [database].[owner].[name].

The SCHEMA argument controls whether an XML Schema Definition (XSD) is included inline with the SOAP response. There is also a SCHEMA option at the endpoint level, so at the WEBMETHOD level we can specify DEFAULT to use the value set at the endpoint level or override it for a particular Web method. If this option is set to STANDARD, the XSD schema is included in the response; if it is set to NONE, it is not.


If you want to receive the result set as a DataSet on the client side, you must set SCHEMA to STANDARD because the schema is required by the client proxy to create the DataSet.

The final option for the WEBMETHOD clause is FORMAT, which is used to control whether row count, error messages, and warnings are returned with the result set. The default, ALL_RESULTS, causes all of these results to be passed back to the client. In addition to controlling what is passed back from the server, the FORMAT argument also affects the interface of the generated proxy when you add a Web reference in Visual Studio .NET. The ALL_RESULTS value causes the proxy to return an object array to the client application. Although it is useful to have access to the row count, error messages, and warnings, receiving an object array requires the client code to do a little more work to get at the desired DataSet. Setting FORMAT to ROWSETS_ONLY changes the Web method signature on the proxy to return a single DataSet, which is simpler for the client code to consume.


The BATCHES option, if set to ENABLED, creates an extra Web method called sqlbatch that allows you to pass a string containing T-SQL commands. Because this gives you broader access to data—for example, enabling you to execute ad hoc SQL queries—the default is DISABLED. Keep in mind that even with BATCHES set to ENABLED, the authenticated user must have rights to the underlying database objects. So if you protect access to all tables in a database and only give rights to stored procedures, turning on BATCHES will allow the user only to invoke multiple stored procedures in a single call—not to execute SQL statements directly against the tables.


The DATABASE argument defines the database the endpoint will be run against. If this argument is not set, it defaults to the DEFAULT database for the login.


You can define the default SOAP namespace for all of the exposed Web methods in an endpoint by including the NAMESPACE argument. Individual Web method entries can override this default by specifying the namespace in the WEBMETHOD clause.


The SCHEMA argument at the endpoint level controls the default value for the SCHEMA argument in contained WEBMETHOD clauses. It defines whether an XSD schema is included inline with the SOAP response. If this option is set to STANDARD, the XSD schema is included in the response; if it is set to NONE, it is not.


The SCHEMA setting is not related to the XMLSCHEMA option in a SELECT query that uses the FOR XML option. If you want the XML returned by a stored procedure to contain a schema, you must specify the XMLSCHEMA option in the T-SQL statement, regardless of the SCHEMA setting here.


The WSDL argument allows you to control how the endpoint generates WSDL to support client applications. If you set this value to NONE, there will be no support for WSDL. If you need to support a custom WSDL format, you can set the WSDL argument to the name of a stored procedure that generates the WSDL and returns it. Due to the complexity of WSDL, implementing the code to return custom WSDL is not a trivial task. You will rarely need to generate custom WSDL, but it’s nice to know the option is there if you need it.

The default value, and most common choice, for the WSDL argument is DEFAULT, which instructs SQL Server to generate WSDL for the endpoint. The WSDL generation actually supports two versions of WSDL for the endpoint. The first is called default WSDL and is returned when you put ?wsdl at the end of the URL (for example, http://MyServer/sql/MyEndpoint?wsdl). This default version of WSDL provides mappings from SQL Server data types to corresponding data types in the .NET Framework 2.0. For example, a number of types in the System.Data.SqlTypes namespace are better suited than the normal .NET primitive types to holding data from SQL Server.

The data type mappings provided in the default WSDL format are useful for client applications built with Visual Studio 2005, but clients written with other technologies might not be able to parse this form of the WSDL. For example, clients written with the SOAP Toolkit cannot understand all of the XSD types in the default WSDL. To support these clients, Native XML Web Services provides the simple WSDL format, which you can obtain by adding the word simple to the end of the Web service URL, as shown here: http://MyServer/sql/MyEndpoint?simple. The simple WSDL format uses only simple XSD types, so it has better backward compatibility with older Web services clients and with non-Microsoft Web services client technologies.

Let’s look at a simple example:

CREATE ENDPOINT my_sql_endpoint
   PATH = '/sql/HelloWorld',
   SITE = 'localhost'
   DATABASE = 'AdventureWorks',
   NAMESPACE = 'http://HelloWorld/'

Here we create the endpoint named my_sql_endpoint. It runs in clear HTTP, and the URL is http://localhost/sql/HelloWorld. It starts out in the STOPPED state. The only Web method exposed is the sqlbatch Web method because BATCHES is enabled. It runs against the AdventureWorks database.


The syntax for ALTER ENDPOINT is pretty much the same as for CREATE ENDPOINT, except you only need to specify the parts that should be changed. You can use ALTER ENDPOINT to add, modify, or drop an existing Web method in the endpoint and to change the properties of the endpoint. For example, you might want to add a new Web method to the endpoint:

ALTER ENDPOINT my_sql_endpoint
FOR SOAP ( ADD WEBMETHOD 'SayHello' (name='AdventureWorks.dbo.SayHello') )

If you created the endpoint with STATE set to STOPPED, you can start the endpoint like this:

ALTER ENDPOINT my_sql_endpoint

Later, you might decide to remove that Web method from the endpoint:

ALTER ENDPOINT my_sql_endpoint


DROP ENDPOINT is used to remove an endpoint definition. The syntax is simple, as you would expect:

DROP ENDPOINT my_sql_endpoint
  •  Western Digital Black 4TB Hard Drive - 4TB Storage Goes Mainstream
  •  Intel Solid State Drive 335 Series - Better Under The Hood
  •  HDD, SSD and Hybrid Hard Drive Competition
  •  SQL Server 2008 : Index analysis (part 3) - Identifying index fragmentation
  •  SQL Server 2008 : Index analysis (part 2) - Identifying indexes to add
  •  SQL Server 2008 : Index analysis (part 1) - Identifying indexes to drop/disable
  •  ADO.NET Programming : Microsoft SQL Server CE (part 5) - Querying Schema Information
  •  ADO.NET Programming : Microsoft SQL Server CE (part 4) - Updating a SQL Server CE Database, The SqlCeDataAdapter Class
  •  ADO.NET Programming : Microsoft SQL Server CE (part 3) - Retrieving and Displaying Data
  •  ADO.NET Programming : Microsoft SQL Server CE (part 2) - SQL Server CE Query Analyzer, Creating a SQL Server CE Database, Populating a SQL Server CE Database
    Top 10
    3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
    3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
    OPEL MERIVA : Making a grand entrance
    FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
    BMW 650i COUPE : Sexy retooling of BMW's 6-series
    BMW 120d; M135i - Finely tuned
    PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
    PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
    Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
    Java Tutorials : Nested For Loop (part 1)
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS