Share via

  • Article

Sample Applications for Sending Native XML Web Services Requests

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The following working samples are provided to show how SOAP requests are sent and responses are processed. These samples include a Visual Studio 2005 client, both C# and Visual Basic.

Initial Setup

All the sample applications require a common setup. This setup includes the following steps:

  1. Create a sample stored procedure.

  2. Create a user-defined function.

  3. Create the HTTP SOAP endpoint.

Note

To create these objects in the AdventureWorks2008R2 sample database, you can use either SQL Server Management Studio or the osql command utility. For information on how to install the AdventureWorks2008R2 sample database, see Considerations for Installing SQL Server Samples and Sample Databases.

Creating the Stored Procedure (GetCustomerInfo)

The following stored procedure is designed to show how a client application handles input and output parameters, return code, query results, and errors if a failure were to occur.

USE AdventureWorks2008R2;
GO
DROP PROCEDURE GetCustomerInfo;
GO
CREATE PROCEDURE GetCustomerInfo
                    @CustomerID nchar(5),
                    @OutputParam nchar(5) OUTPUT 
AS  

  SELECT @OutputParam = '99999'  
  -- The following INSERT should fail, and an-error returned 
  -- to the client.
  INSERT Store (CustomerID) VALUES (1)

 -- Execute a SELECT statement.
 SELECT top 3 SalesOrderID, OrderDate 
 FROM   Sales.SalesOrderHeader
 WHERE  CustomerID = @CustomerID

 -- Execute SELECT returning XML.
  SELECT CustomerID, SalesOrderID, OrderDate 
  FROM   Sales.SalesOrderHeader
  WHERE  CustomerID = @CustomerID
  for xml auto, XMLSCHEMA

  PRINT 'Hello World'
RETURN 0;
Go

This stored procedure has two parameters: an input parameter (CustomerID) and an output parameter (OutParam) to show how parameters are passed.

The stored procedure executes the following statements:

  • An INSERT statement that intentionally fails, returning an error. The error is intentional to show how errors are returned in the SOAP response. In Visual Studio 2005, the errors are returned as SqlMessage objects.

  • A SELECT statement. In Visual Studio 2005, the resulting rowset is returned as a DataSet object.

  • A SELECT FOR XML query that returns XML data. In Visual Studio 2005, the resulting rowset is returned as a SqlXml object.

  • A PRINT statement. The result of this is returned in Visual Studio 2005 as a SqlMessage object.

Creating the User-defined Function (UDFREturningAScalar)

This function returns an integer value.

USE AdventureWorks2008R2;
GO
CREATE FUNCTION UDFReturningAScalar() 
RETURNS int
AS
BEGIN
   RETURN 555
END;
Go

Creating the HTTP SOAP Endpoint (sql_endpoint)

This endpoint exposes the stored procedure and the user-defined function as Web methods. The endpoint is also configured to allow for ad hoc queries. The client application can send SOAP requests for ad hoc queries to the endpoint.

Note that you will have to provide the server name as the value of hostname when you reserve the HTTP namespace (sp_reserve_http_namespace) for your endpoint and also when you complete the SITE parameter. Also, the value of port must correspond to the HTTP port used by the instance of SQL Server, such as "80" or another TCP port number if it applies.

USE AdventureWorks2008R2;
GO
DROP ENDPOINT sql_endpoint;
GO
EXEC sp_reserve_http_namespace N'http://hostname:port/sql' ;
-- EXEC sp_reserve_http_namespace N'https://www.microsoft.com:80/sql' for example
GO
CREATE ENDPOINT sql_endpoint 
   STATE = STARTED
AS HTTP(
   PATH = '/sql', 
   AUTHENTICATION = (INTEGRATED ), 
   PORTS = ( CLEAR ),
   SITE = 'server'
)
FOR SOAP (
   WEBMETHOD 'http://tempUri.org/'.'GetCustomerInfo' 
            (name='AdventureWorks2008R2.dbo.GetCustomerInfo', 
             schema=STANDARD ),
   WEBMETHOD 'UDFReturningAScalar' 
            (name='AdventureWorks2008R2.dbo.UDFReturningAScalar'),
   BATCHES = ENABLED,
   WSDL = DEFAULT,
   DATABASE = 'AdventureWorks2008R2',
   NAMESPACE = 'http://Adventure-Works/Customers'
);
GO
USE master;
EXEC sp_grantlogin @loginame='domain\userOrGroup';
EXEC sp_grantdbaccess @loginame='domain\userOrGroup';
GRANT CONNECT ON ENDPOINT::sql_endpoint TO [domain\userOrGroup];
GO

In the previous code, note the following:

  • There are two Web methods that are defined in creating this endpoint.

  • The WSDL value is set to DEFAULT. Therefore, the client can request a WSDL response from the server.

  • The BATCHES value is set to ENABLED. Therefore, the client can send ad hoc query requests to this endpoint.

  • The Web method UDFReturningAScalar does not specify the optional namespace. It uses the namespace specified in the NAMESPACE value.

  • The values of domain\userOrGroup that are created as a SQL Server login should be set to the actual domain user or group that requires access to the HTTP endpoint.