SQL Server

SQL Server 2008: SQL Server Web Services - Building Web Services (part 3)

1/2/2011 11:49:18 AM

The FOR SOAP Keyword Group

The second major section of the DDL begins after the end parenthesis of the AS clause, with the FOR SOAP group, whose keywords appear in parentheses.

First, you assign the namespaced-name 'urn:www-samspublishing-com:examples'.'WM_GetEmployeeBasics' to WEBMETHOD. This name is specified in two parts to ensure its uniqueness:

  • A namespace as a string in URN format (followed by a period)

  • The string name of the web method


In Listing 3, the naming convention WM_ is used simply to differentiate the web method from other database objects.

The following keyword options are used inside the parenthetical group following WEBMETHOD:

  • NAME— The string value represents the SQL Server scalar-valued user-defined function (UDF) or stored procedure that will be executed via the web service.

  • SCHEMA— This keyword choice describes the quality of XML schema produced to describe the transmitted XML data.


The SCHEMA keyword occurs twice in the FOR SOAP group. This first occurrence of SCHEMA relates specifically to inline schema generation for the web method. It tells the compiler how to generate (or not generate) an XSD schema within the SOAP response envelope that describes the types used by this particular WEBMETHOD.

These are the valid keyword values for SCHEMA:

  • NONE—Do not include web method–specific schema information in the SOAP response.

  • STANDARD—Generate a standard schema.

  • DEFAULT—Use the value of the SCHEMA keyword that is specified (somewhat confusingly, a second time) after the end of the WEBMETHOD clause.

  • FORMAT— This option specifies which kinds of objects are returned to the web method’s caller. Following are the valid keyword values for FORMAT:

    • ALL_RESULTS—Include two or more objects in the SOAP response, including the following:

      • The result set itself (in .NET, deserialized as DataSet; or, in the case of web methods that return XML, one or more sqlresultstream:SqlXml nodes deserialized as XmlElements)

      • A row count of the result set (in .NET, deserialized as a SqlRowCount object; or, in the case of XML results, a sqlresultstream:SqlRowCountXmlElement) node deserialized as an

      • A result code (in .NET, an integer; or, in the case of XML results, a sqlresultstream:SqlResultCode node deserialzed as an XmlElement)

      • Any SQL Server errors or warnings, if generated at runtime (in .NET, deserialized as SqlMessage objects)

    • ROWSETS_ONLY—Return just the result sets, if any.

    • NONE—Do not mark up the output data in SOAP-typed envelope data. NONE is an advanced setting and should be used with the following caveat: no output parameters or UDFs are allowed with this option, and WSDL for the web method is not generated.

  • BATCHES— Setting this switch to ENABLED or DISABLED allows or disallows ad hoc T-SQL statements to be executed on the endpoint. This means that any number of SQL statements (with associated parameters) may be run via the special sqlbatch() web service proxy method.


    Although convenient, the BATCHES feature has some security implications because a wide range of T-SQL may be executed; thus, many administrators want it kept off. (It is disabled by default.) There are, however, some valid situations for using it, including

    • During the design and testing phases of a website

    • When implementing highly customized remote database administrative tools

    • For ad hoc-query–dependent features

  • LOGIN_TYPE— You use this setting to set the SQL Server Authentication mode for the endpoint to either MIXED (both Windows and SQL Server) or WINDOWS (the default). As with BASIC authentication, SSL is required to be both implemented on the server and specified after the PORTS keyword for the statement to compile.

  • WSDL— You use this setting to determine whether SQL Server will generate WSDL for methods on the endpoint. You specify DEFAULT to do so or NONE. When you require specific WSDL to be generated, you specify a string value corresponding to the name of the custom stored procedure that generates the home-grown WSDL. Here’s an example:

    WSDL 'wsdl_generating_stored_procedure_name'


The built-in system stored procedures that SQL Server uses to generate WSDL are sp_http_generate_wsdl_complex, sp_http_generate_wsdl_simple, sp_http_generate_wsdl_defaultcomplexorsimple, and sp_http_generate_wsdl_defaultsimpleorcomplex. You can test them by executing them with varying parameters to see how they work.

WSDL on SQL Server comes in two different flavors: the default and simple. To see an example of simple WSDL, try the following URL (after you create the sample endpoint by running the code in Listing 3): http[s]://ServerDomainName/opensql?wsdlsimple.
  • SESSIONS— You use this setting to specify whether SOAP sessions managed by SQL Server are ENABLED or DISABLED (the default). Managing SOAP sessions on the client side requires a fair amount of programming in an environment such as Visual Studio 2008 for successful implementation. Not all SOAP clients require sessions.

  • SESSION_TIMEOUT— You use this setting to specify how long (in seconds) before a SQL Server SOAP session times out.

  • DATABASE— You use this setting to specify the database (named with a string value) in whose context the web methods of this endpoint are executed. Note that the keyword DEFAULT is also an option. Using it tells SQL Server to execute the web methods in the context of the default database of the login accessing the endpoint.

  • SCHEMA— This is the second occurrence of this keyword in the CREATE ENDPOINT DDL. This time around, it applies to schema generation for all SOAP responses of all web methods on the endpoint, not merely of a particular web method. These are the possible values:

    • NONE—Do not generate an inline XML schema in the SOAP response.

    • STANDARD—Generate an inline schema.

  • CHARACTER_SET— The XML specification specifies a set of characters that are invalid in element and attribute values; they are <, >, ", ', and &. The reason for this is that XML parsers would have a hard time figuring out whether these characters represent markup or text values because they are used to delimit XML information items. For example, they are used in element tagging (for example, <element>), attribute naming (for example, attribute="value "), and entity naming (for example, &entity;).

    The two keyword values for CHARACTER_SET treat these and other special characters (when found in markup) in distinct ways:

    • XML—If a SOAP response is sent and the special XML characters are not escaped into their valid entity equivalents (&lt;, &gt;, &quot;, &apos;, and &amp;) the response generates an error. This is the default.

    • SQL—Any invalid XML characters are transformed into their respective entity representations (a process called entitization) before response transmission.

    Other special characters are not permitted in the names of XML attributes or elements (known formally as qualifying names, or QNames) but may nevertheless end up in XML documents. SQL Server automatically escapes them by taking their Unicode hexadecimal values and preceding them with the string #x[4-digit hex value]. The asterisk (*) character, for example, would be converted to the character string #x002A.


This character conversion may not be cross-platform compatible because not all parsers approve of it, but it is far better to convert characters than have your SQL connection closed due to an XML parsing error.

One of the ways that such special character entitization may occur is when you use SQL column (or other object) aliases that, though legally named in T-SQL, are not valid QNames (for example, SELECT ColumnName AS '*' FROM TableName FOR XML RAW).

  • MAX_SOAP_HEADERS_SIZE— Optionally, you can set the maximum size of the header section of each transmitted SOAP envelope. (The default is 8KB.) Transmitting a larger header than specified in this setting thus causes a server error.

As you can see, the CREATE ENDPOINT syntax offers a feast of options. Thankfully, it is easy to choose the ones you need, depending on your application’s requirements.

Other -----------------
- SQL Server 2008: SQL Server Web Services
- SQL Server 2008: SQL Server Service Broker - Related System Catalogs
- SQL Azure Backup Strategies (part 2)
- SQL Azure Backup Strategies (part 1) - Copying a Database
- SQL Server 2008: Troubleshooting SSB Applications with ssbdiagnose.exe
- SQL Server 2008: Service Broker Routing and Security
- Migrating Databases and Data to SQL Azure (part 9)
- Migrating Databases and Data to SQL Azure (part 8)
- Understanding Service Broker Constructs (part 5)
- Understanding Service Broker Constructs (part 4) - Creating the Conversation Initiator
- Migrating Databases and Data to SQL Azure (part 7)
- Migrating Databases and Data to SQL Azure (part 6) - Building a Migration Package
- Migrating Databases and Data to SQL Azure (part 5) - Creating an Integration Services Project
- Understanding Service Broker Constructs (part 3)
- Understanding Service Broker Constructs (part 2) - Creating Queues for Message Storage
- Understanding Service Broker Constructs (part 1) - Defining Messages and Choosing a Message Type
- SQL Server 2008 : SQL Server Service Broker - Designing a Sample System
- Migrating Databases and Data to SQL Azure (part 4) - Fixing the Script
- Migrating Databases and Data to SQL Azure (part 3) - Reviewing the Generated Script
- SQL Server 2008 : SQL Server Service Broker - Understanding Distributed Messaging
Most View
- SharePoint 2010 : Add a Column to a List or Document Library
- Windows Phone 7 : Uninstalling an App
- jQuery 1.3 : AJAX and events & Security limitations
- SQL Server 2008 R2 : Replication - The Publisher, Distributor, and Subscriber Magazine Metaphor
- Exchange Server 2010 : Planning Certificates for Autodiscover (part 1) - The X.509 Certificate Standard
- Performing SharePoint 2010 Installations (part 3)
- WCF Security Concepts
- SharePoint 2010 : Get to a Site’s Permission Management Page
- Protecting SQL Server Data : CELL-LEVEL ENCRYPTION - Views and Stored Procedures (part 1) - Creating the View
- Windows 7 : Understanding User Account Control (part 2) - Configuring User Account Control
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS