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 AdventureWorks sample database, you can use either SQL Server Management Studio or the osql command utility. For information on how to install the AdventureWorks sample database, see Considerations for Installing SQL Server Samples and Sample Databases.

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.
  

This function returns an integer value.

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

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'http://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.
  

• Sending SOAP Requests by Using Visual Studio 2005 Client (C#)
  
• Sending SOAP Requests by Using Visual Studio 2005 Client (Visual Basic)
  
• Adding SOAP Headers to Client Applications
  
• Adding SOAP Trace Support to Client Applications
  
• Handling the xml Data Type and CLR User-defined Types
  

Reference

Other Resources

Help and Information