Sample Applications for Sending Native XML Web Services Requests
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.
Sending SOAP Requests by Using Visual Studio 2005 Client (C#)
Sending SOAP Requests by Using Visual Studio 2005 Client (Visual Basic)
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.
|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.|
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:
INSERTstatement 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.
SELECTstatement. In Visual Studio 2005, the resulting rowset is returned as a DataSet object.
SELECT FOR XMLquery that returns XML data. In Visual Studio 2005, the resulting rowset is returned as a SqlXml 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.
WSDLvalue is set to
DEFAULT. Therefore, the client can request a WSDL response from the server.
BATCHESvalue is set to
ENABLED. Therefore, the client can send ad hoc query requests to this endpoint.
The Web method
UDFReturningAScalardoes not specify the optional namespace. It uses the namespace specified in the
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.
ReferenceAdding SOAP Headers to Client Applications
Adding SOAP Trace Support to Client Applications
Setting the Server to Listen for Native XML Web Services Requests
SOAP Request and Response Message Structure
Guidelines and Limitations in Native XML Web Services