Article ID: 190987
Article Last Modified on 5/9/2006
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q190987
SUMMARY
This article describes the proper creation and implementation of SQL Server extended stored procedures. Additionally, this article provides details and references to perform a successful implementation of SQL Server extended stored procedures.
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:
Extended stored procedures provide a way to dynamically load and execute a function within a dynamic-link library (DLL) in a manner similar to that of a stored procedure, seamlessly extending SQL Server functionality. Actions outside of SQL Server can be easily triggered and external information returned to SQL Server. Return status codes and output parameters (identical to their counterparts in regular stored procedures) are also supported. SQL Server includes system stored procedures that add (sp_addextendedproc), drop (sp_dropextendedproc), and provide information about (sp_helpextededproc) extended stored procedures.
You must treat the extended stored procedure DLL as any other DLL development: It is shared code, and multiple threads can access it at the same time. As with any production-worthy project, make sure to use thorough design and complete testing.
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"
For more information about how to write security-enhanced code when you are using extended stored procedures, see Writing Secure Code by Michael Howard and David LeBlanc. For more information about this book, visit the following Microsoft Web site:
Registration
The 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:
Extended stored procedures are registered in the master database, and the system administrator (SA) maintains control over their usage and registration.
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:
151596 Extended procedure error: "Cannot find the DLL 'xxx.dll'"
The address space
SQL 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.
Example
char strName[31] = "";
strncpy(strName, "Bob", 35); // <-- Incorrect length
In this example, you are incorrectly copying data past the end of the strName buffer. The documentation for the strncpy routine states that it will copy the second string into the strName parameter, and then zero fill the rest of the buffer. Therefore, the example is writing 35 bytes, even when the second string is 3 bytes in length. The strncpy routine most likely will not cause an access violation because you are still in the process address space. However, the operation might have easily corrupted an internal memory structure, which would have caused unexpected process behavior. In the case of the SQL Server process, this type of mistake might corrupt a critical internal SQL Server structure, and, as such, might show itself through dropped connections or other unexpected SQL Server behavior. Additionally, the server may stop responding.
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 leaks
Any 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 safety
Applications 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:
163449 Use of Thread Local Storage in an extended stored procedure
Structure exception handling
You 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 connections
A 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.
SQL Server 6.5 and later builds of SQL Server support bound connections. See srv_getbindtoken and sp_bindsession for implementation details. Binding the loopback connection to the original connection places both connections in the same transaction space. This means that the block that originally occurred in the Sales table can be avoided.
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:
162361 Understanding and resolving SQL Server 6.x blocking problems
180775 Client effects on SQL Server throughput
Errors and messages
Another 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:
174817 Microsoft SQL Server DB-Library has limited extensibility
With the Open Data Services API call srv_message_handler, you can place text in the SQL Server error log. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
164290 FIX: Srv_message_handler text limit
One final note about the DB-Library error handler: You can return the INT_EXIT value from the installed callback function. However, as documented, it causes the application to EXIT. This means that you are instructing the process to EXIT. Therefore, it must not be called from a DLL because of the effects to applications such as IIS or SQL Server.
Transact-SQL KILL
Another 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 settings
Never 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_Senddone
By 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 termination
When 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.
REFERENCES
Richter, Jeffrey. Advanced Windows. Redmond, WA: Microsoft Press, 1997
Howard, Michael and David LeBlanc. Writing Secure Code, Second Edition. Redmond, WA: Microsoft Press, 2002.
Additional query words: xproc xprocs sproc sprocs st proc procs ODS dblib tsql transql
Keywords: kbhowtomaster kbprogramming KB190987
