Extended stored procedures are a very powerful way to extend the functionality of SQL Server. The following excerpt is from the SQL Server Books Online:
To write successful extended stored procedures, you need a working knowledge of many topics. To review these topics, see the following chapters in Advanced Windows by Jeffrey Richter:
- Chapter 3, "Processes"
- Chapter 4, "Threads"
- Chapter 10, "Thread Synchronization"
- Chapter 12, "Dynamic-Link Libraries"
- Chapter 13, "Thread Local Storage"
- Chapter 16, "Structured Exception Handling"
RegistrationThe extended stored procedure architecture is not complicated; it is a Microsoft Visual C or C++ compatible DLL that is linked with the Opends60.lib file that exposes the properly exported function (or functions). You use the sp_addextendedproc stored procedure to register the exported function name and the associated DLL. See the xp, xp_dblib, and xp_odbc samples that are in the SQL Server Programmer's Toolkit for examples. To access the SQL Server Programmer's Toolkit, visit the SQL Server page at the following Microsoft Web site:
When you register your DLL, make sure that it is in the current system path and that it follows the 8.3 file-naming convention.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
The address spaceSQL Server uses LoadLibrary, GetModuleHandle, and GetProcAddress to obtain a pointer to the exported function, and then it passes the function a SRVPROC structure. After the DLL receives the SRVPROC structure, you can perform standard Open Data Services operations to obtain parameters and to return results to the caller.
As a DLL, it is loaded in the address space of the calling process. In the case of the extended stored procedure, the process is SQL Server. If a DLL is improperly accessing memory or if it is not thread safe, you can adversely affect the process. You must perform thorough testing to make sure that the DLL maintains the integrity of the process. If you are at all concerned that an extended stored procedure may be adversely affecting SQL Server, you must address this problem immediately.
For example, you can use the Microsoft Visual C or Microsoft Visual C++ wizards to create a DevStudio Add-in Wizard. This wizard is an In-Process COM server, or DLL. If you do not write your wizard properly, it can adversely affect the process.
char strName = "";
strncpy(strName, "Bob", 35); // <-- Incorrect length
SQL Server tries to protect the address space. Invocation of an extended stored procedure is wrapped in a try/except block, and many points in the code perform minimal runtime correctness checking. It is important to remember that the protection is provided by a try/except block and not by a try/catch block. Therefore, the code will not perform stack unwinding for objects.
Memory leaksAny project may have a problem where allocated memory, a handle, or a similar resource is not being released properly. It is paramount to any DLL test suite that the suite makes sure that the DLL is releasing all resources correctly. These types of issues are likely to show themselves as increased page file usage, altered performance, or increased paging.
Thread safetyApplications such as Microsoft Internet Information Server (IIS) and Microsoft SQL Server are thread pooling, multi-threaded applications. This means that you can start your DLL from multiple connections at the same time, particularly on a computer that has multiple processors. This also means that a single connection can invoke different entry points of the DLL (XPROC, ISAPI) from a different worker thread. Thread pooling can limit the usefulness of Thread Local Storage (TLS) variables.
Make sure that all code paths are thread-safe and re-entrant by linking with multi-threaded run-time libraries, and by making sure that all vendor DLLs that you are using are thread-safe.
For more information about thread local storage and a detailed account of thread safety issues, click the following article number to view the article in the Microsoft Knowledge Base:
Structure exception handlingYou must also clearly understand structured exception error handling. Every entry point in a DLL must properly account for exception errors. SQL Server tries to catch exception errors but any DLL has to capture and handle exception errors properly. Specifically, any threads that are started in a DLL must install structured exception error handlers.
Each thread in a process has an exception stack. However, if the DLL starts a new thread, it starts its exception naked. If the thread does not install a try/except or a try/catch block immediately, the thread is only protected by the operating system. Any exception error that the thread encounters is considered unhanded and fatal to the whole process. Remember, the DLL is in the process space of the caller and this type of issue will cause a fatal exception to the process.
SQL Server and associated components of SQL Server are linked with the runtime DLL versions. Any extended stored procedure that you develop must also be linked with the runtime DLL versions.
Loopback connectionsA loopback connection is made when the extended stored procedure makes a connection back to the same computer that is running SQL Server. These are described in the xp_dblib and xp_odbc samples, which are included with the SQL Server Programmer's Toolkit.
You can only perform loopback connections on bound sessions. One problem with a loopback connection is that it is a new connection and is therefore in a separate transaction space. For example, suppose the extended stored procedure performs a complex mathematical calculation on the sales table. The loopback connection tries to complete a SELECT statement on the sales table. However, the original connection had performed an UPDATE statement to the Sales table. Unless you have taken diligent care to implement a query timeout, to perform asynchronous query processing, and to check SRV_GOTATTENTION, this connection might block itself.
Note SQL Server only supports loopback connections on bound sessions.
For more information about blocking issues, click the following article numbers to view the articles in the Microsoft Knowledge Base:
Errors and messagesAnother facet of a loopback connection or an extended stored procedure that makes a connection to another computer that is running SQL Server or to an Open Data Services gateway is the handling of errors and messages.
If you are using DB-Library, you must use per-process error and message handlers. SQL Server controls the global message handlers and an extended stored procedure must not replace them. Per-process error and message handlers are also guaranteed to be thread-safe. See dbprocmsghandle and dbprocerrhandle for complete details.
Hint Install them in the LOGINREC before calling dbopen.
For more information about the limitation of DB-Library usage in an extended environment, click the following article number to view the article in the Microsoft Knowledge Base:
Transact-SQL KILLAnother aspect of the loopback connection or extended stored procedure execution in general is the use of the Transact-SQL KILL statement. Because the KILL statement is Transact-SQL based, the current Open Data Services API set has no knowledge of the Transact-SQL KILL status. An extended stored procedure must look for SRV_GOTATTENTION so it can handle requests from the client to cancel the operation. However, the SA currently cannot issue a Transact-SQL KILL statement to interrupt the execution of an extended stored procedure. This makes it more important that you properly use bound connections and good coding practices. A Design Change Request (DCR) has been filed with SQL Server development to extend the functionality of the Transact-SQL KILL statement to extended stored procedures.
Global settingsNever affect the global state of a process from a DLL. For example, SQL Server specifically calls the Win32 API call SetErrorMode to set the desired behavior. An extended stored procedure must never call SetErrorMode or other process global calls because this is global to the process space. There are several other calls that globally affect a process; make sure that the DLL does not use these calls.
Additionally, certain Open Data Services (ODS) calls are designed solely for use in an ODS-based application and must not be used in an extended stored procedure. These include calls such as srv_init, srv_config, srv_handle and srv_errhandle. Calling these functions overrides the values that SQL Server installs and may lead to unpredictable failure conditions.
Srv_SenddoneBy default, SQL Server automatically calls the srv_senddone extended stored procedure with the SRV_DONE_FINAL flag on return from the invocation of an extended stored procedure. The extended stored procedure must not call srv_senddone with the SRV_DONE_FINAL flag; instead, it must use the SRV_DONE_MORE flag.
String terminationWhen you are dealing with strings that are returned from the Open Data Services API, you must always make sure that termination occurs. A string that is returned from the srv_paramdata extended stored procedure is not guaranteed to be NULL terminated. You must use the srv_paramlen extended stored procedure to properly manipulate the strings. Other Open Data Services functions may be similar; test them thoroughly.
Howard, Michael and David LeBlanc. Writing Secure Code, Second Edition. Redmond, WA: Microsoft Press, 2002.
Article ID: 190987 - Last Review: Sep 12, 2008 - Revision: 1