Dela via

DAT329: SQL Server 2005 Web Services

  • Artikel

SQL Server 2005 contains a completely rearchitected web services layer. Rather than relying on IIS to provide the web protocol stack, Yukon merely uses HTTP.SYS (the new kernel mode driver in Windows Server 2003 and Windows XP Service Pack 2). This means you don't need IIS to be installed, which will be a great boon to many database administrators.

Architecturally, to generate a web service, you create an endpoint to a stored procedure that can be accessed as a remote procedure call or via batch processes. Lastly you can request WSDL from this endpoint, at which point the server invokes an internal stored procedure to generate the relevant WSDL.

Endpoints
What is an endpoint? It's simply a point of entry into SQL Server. As an abstraction, it combines both a transport and the payload, in two separate sections. For example:

    CREATE ENDPOINT Demo 
   AS HTTP ( -- transport
      authentication = (integrated), 
      path = '/sql/demo', 
      ports = (clear), 
      state = started ) 
   FOR SOAP ( -- payload
      webmethod 'https://test.ms.com'.'testproc1' (name=demodb.dbo.testproc1),
      batches = enabled, 
      wsdl = default )

So an endpoint has a name, a set of options for the transport (e.g. the URL), and a set of options for the payload (e.g. the methods callable at that URL).

Endpoints are therefore not protocol-specific, but could be extended to include other transports such as TCP. Anonymous access to endpoints is not supported - all calls must use basic, digest or integrated authentication, or via SQL Auth using WS-Security.

Generating WSDL
The WSDL describes the application (services) that an endpoint exposes. A request for WSDL follows a similar format to ASP.NET - the URL including path, suffixed with ?wsdl. Out of the box, we support two types of WSDL: either using full rich types or a simplified version that increases interoperability (use ?wsdlsimple instead).

You can also extend this by creating your own custom stored procedure to generate WSDL, by setting the wsdl property in the payload element of the DDL to point to the sproc. Internally you can use sys.http_generate_wsdl_default to generate the original WSDL and then customise it. There will be a KB article on generating custom WSDL  for different interoperability clients such as JBuilder or GLUE as well as Everett and Whidbey.

The type for the SOAP response you receive varies depending on what kind of result is being returned. For a user-defined function, the result is simple, for example varchar(100). For a stored procedure or batch execution, however, there is no fixed schema for what will be returned, so what does the result set contain? SQL Server 2005 generates a ResultStream element that can contain one or more of the following types as part of the stream:

  • SqlRowSet - the output of a SELECT statement
  • SqlXml - the output of a SELECT ... FOR XML statement
  • SqlMessage - the output of error, warning, print messages sent to the output pipe
  • SqlRowCount - the number of rows affected
  • SqlResultCode - the return code for a s/proc

Note, for errors and faults, a standard HTTP error or SOAP fault is returned.

Accessing Endpoints

Sessions can be enabled on an endpoint, and are analogous to cookies. Sessions allow multiple executions to share the same context despite HTTP being an inherently stateless protocol. Interestingly, you can terminate and reopen a session within the SOAP header: this is different to TDS, where a session is fixed to last as long as a connection.

You can access a SQL endpoint from VS.NET by adding a web reference. If you enable batches, you can send dynamic SQL using a sqlbatch method; other functions and s/procs will appear in the proxy using their name generated in the CREATE ENDPOINT statement. To make it easier for your client, you can set a hint on the endpoint that the result stream from a s/proc will only contain rowsets: FORMAT=ROWSETS_ONLY. Rather than returning an object array, the result from the execution will then be a DataSet.

Comments