Extended Stored Procedure Architecture
Microsoft® SQL Server™ 2000 extended stored procedures extend Transact-SQL functionality by enabling you to implement logic in functions contained in dynamic-link library (DLL) files, and call those functions from Transact-SQL statements just as you would a Transact-SQL procedure. Dynamic-link library files have the .dll file name extension. Extended stored procedures can include most of the features of Microsoft Win32® and COM applications.
A DLL file must conform to the Extended Stored Procedure API to operate as an extended stored procedure. The DLL can contain multiple functions identified to SQL Server as extended stored procedures; each function is identified by a separate extended stored procedure name. When a Transact-SQL statement references one of the extended stored procedures, the relational database engine calls the function associated with the extended stored procedure name. Extended stored procedures can open a connection back to the SQL Server instance that called them, or connect to remote SQL Server installations.
Extended stored procedures are a part of the Open Data Services layer of the relational database engine, which is the interface between the engine and the server Net-libraries. The server Net-Libraries receive client TDS packets and pass them to Open Data Services. Open Data Services transforms the TDS packets into events that it passes to other parts of the relational database engine. The database engine then uses Open Data Services to send replies back to SQL Server clients through the server Net-Libraries.
When the relational database engine determines that a Transact-SQL statement references an extended stored procedure:
- The relational database engine passes the extended stored procedure request to the Open Data Services layer.
- Open Data Services then loads the DLL containing the extended stored procedure function into the SQL Server 2000 address space, if not already loaded.
- Open Data Services passes the request to the extended stored procedure.
- Open Data Services returns the results of the operation to the database engine.
In the past, The Open Data Services API was also used to write server applications, such as gateways to other database systems. These types of applications have been replaced by newer technologies such as:
- Database APIs that support multiple different databases and other data sources, such as OLE DB and ODBC.
Applications written to the OLE DB or ODBC APIs have little need for a gateway to access different databases.
- SQL Server 2000 supports heterogeneous distributed queries, which allow Transact-SQL queries to pull data from any OLE DB data source without any need for specialized server applications.
- MS DTC, which allows distributed transactions to span multiple databases.
- Windows NT Component Services, for running midtier application logic.
SQL Server 2000 does not support the obsolete portions of the Open Data Services API. The only part of the original Open Data Services API still supported by SQL Server 2000 are the extended stored procedure functions, so the API has been renamed to the Extended Stored Procedure API.