Exposing SQL Programmability to the Web
When you create or update HTTP SOAP endpoints, stored procedures or user-defined functions can be selectively exposed as Web methods. Web methods are existing stored procedures or user-defined functions that have already been created by using the appropriate Transact-SQL statement, such as CREATE PROCEDURE for a stored procedure or CREATE FUNCTION for a user-defined function, and that are defined for Web access through the endpoint.
After a stored procedure or user-defined has been created at the server, it can subsequently be publicly exposed as a Web method by using the endpoint DDL statements, CREATE ENDPOINT or ALTER ENDPOINT.
Parameter Naming Guidelines and Limitations for Web Methods
According to naming rules for SQL Server 2005, parameters that are used in user-defined functions and stored procedures must have correctly formatted names. For more information, see Using Identifiers As Object Names and Specifying a Parameter Name.
Sometimes, certain types of deprecated naming conventions are allowed for user-defined functions and stored procedure names that are not acceptable for use with Native XML Web Services access. Parameter names that are allowed by SQL Server parameter naming rules but have been deprecated and not allowed when the user-defined function or stored procedure is exposed as a Web method include the following:
@
@@
@@this_is_not_a_global
To allow for exposure through Native XML Web Services access, these deprecated parameter names must be modified for the requested procedure or function to allow for parameters that have standard names of the form @param to be used.
Exposing System Stored Procedures
In SQL Server 2005, system stored procedures can be used to perform several informational and administrative activities. Sometimes, you may want to expose these procedures as Web service methods that can be accessed through HTTP endpoints.
All system-stored procedures are stored in the system Resource database, but not all system stored procedures have metadata information that will allow server-generated Web Service Description Language (WSDL) responses to expose the correct schema. This can create situations in which some system stored procedures do not operate as expected when they are exposed on an HTTP endpoint.
If you intend to expose system stored procedures as part of your own custom SQL Server management solution, you may use the following alternative approaches. These let you to work around situations where a specific system stored procedure cannot be made accessible.
- First, for simple nonrecursive use of system stored procedures, try creating your own user-defined stored procedures for wrapping the system stored procedure you want to expose on an HTTP endpoint.
- Second, if the previous approach proves too complex or unwieldy to manage because you are using nested calls to multiple stored procedures, consider using the sqlbatch method instead as a workaround. For an example of how to use sqlbatch, see SOAP Request Message Structure.
See Also
Other Resources
CREATE ENDPOINT (Transact-SQL)
ALTER ENDPOINT (Transact-SQL)
DROP ENDPOINT (Transact-SQL)