Microsoft SQL Server for Windows NT supports extended stored procedures, allowing functions that you develop using the Open Data Services API to be integrated into SQL Server as a dynamic component of SQL Server, instead of providing them as part of a separate server application. Users call these procedures in the same way that they call standard SQL Server stored procedures.
Extended stored procedures extend SQL Server by providing functions that are similar to those of an Open Data Services server application, such as accessing data or running commands that exist outside of the standard SQL Server environment.
You create extended stored procedures using your own C application code with the Open Data Services API in the same way as you would create a separate Open Data Services server application. Rather than responding to a series of requests from connected clients, however, extended stored procedures use the Open Data Services API to respond to procedure calls from individual clients and servers and then return the appropriate results.
Integrating your Open Data Services server application functions directly into SQL Server as extended stored procedures offers the following advantages:
For the SQL Server user, executing an extended stored procedure is similar to executing a stored procedure.
For the SQL Server system administrator, adding and dropping extended stored procedures is similar to adding and dropping stored procedures.
Extended stored procedures are implemented as a dynamic-link library (DLL) that runs as part of SQL Server, sharing the SQL Server address space and security privileges.
Note Only a SQL Server system administrator can add extended stored procedures to SQL Server. The administrator should thoroughly test these procedures before installation due to the potential for any process that shares the same address space in system memory to affect normal SQL Server processing. Although SQL Server uses an exception handler to catch memory access violations, it is not always possible to detect cases in which an extended stored procedure corrupts a portion of memory that is being used by SQL Server.
The alternative to running extended stored procedures in the same address space as SQL Server is to provide these functions as a separate Open Data Services server application. For more information, see Sample Application-Procedure Server.
The XP.DLL sample application provides the following extended stored procedures, which can be called either from a client or from SQL Server.
| Extended stored procedure |
Description |
|---|---|
| xp_diskfree | Accepts a drive letter as an argument and returns the amount of available disk space as a return parameter. |
| xp_disklist | Returns a list of available disk drives and the amount of available disk space for each one as a relational results set. |
| xp_echo | Accepts an input parameter as an argument and returns the same value as an output parameter. |
| xp_proclist | Returns a list of available extended stored procedures. |
| xp_scan_xbase | Returns the contents of a dBASEŽ file as if it were a SQL Server query results set, performing the equivalent of a SELECT * from tablename statement. |