Sample Applications for Sending Native XML Web Services Requests

Article
12/03/2008

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:

Create a sample stored procedure.
Create a user-defined function.
Create the HTTP SOAP endpoint.

Note

To create these objects in the AdventureWorks sample database, you can use either SQL Server Management Studio or the osql command utility. To install the AdventureWorks sample database, see Running Setup to Install AdventureWorks Sample Databases and Samples.

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 AdventureWorks
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 AdventureWorks
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 AdventureWorks
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='AdventureWorks.dbo.GetCustomerInfo', 
             schema=STANDARD ),
   WEBMETHOD 'UDFReturningAScalar' 
            (name='AdventureWorks.dbo.UDFReturningAScalar'),
   BATCHES = ENABLED,
   WSDL = DEFAULT,
   DATABASE = 'AdventureWorks',
   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.

Share via

Sample Applications for Sending Native XML Web Services Requests

Initial Setup

Creating the Stored Procedure (GetCustomerInfo)

Creating the User-defined Function (UDFREturningAScalar)

Creating the HTTP SOAP Endpoint (sql_endpoint)

See Also

Reference

Concepts

Other Resources

Help and Information

Additional resources