INF: Extended Stored Procedures: What Everyone Should Know

ID: Q190987

The information in this article applies to:

SUMMARY

Extended stored procedures are a very powerful way to extend the functionality of SQL Server. The following paragraph is taken from the 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.

This article is intended to present the proper creation and implementation of SQL Server extended stored procedures. It contains details and references to ensure a successful implementation.

The extended stored procedure DLL should be treated like any other DLL development effort. It is shared code, and multiple threads can access it at the same time. Like any production worthy project, thorough design and complete testing should be the rule.

To write successful extended stored procedures, you should have a working knowledge of many topics. The following chapters in "Advanced Windows" by Jeffrey Richter cover the topics well: 

   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


MORE INFORMATION

The extended stored procedure architecture is not complicated. Simply stated, it is a Microsoft Visual C or C++ compatible DLL, linked with the Opends60.lib file and exposing the properly exported function(s). You use the sp_addextendedproc to register the exported function name and associated DLL. See the xp, xp_dblib, and xp_odbc samples contained in the SQL Server Programmer's Toolkit for examples. You can get the SQL Server Programmer's Toolkit from the SQL Server page on the Microsoft Web site at:

http://www.microsoft.com/sql/default.asp

Registration

Extended stored procedures are registered in the master database, and the system administrator (SA) maintains control over their usage and registration.

When registering your DLL it is best to make sure that it is in the current system path and that conforms to the 8.3 file naming convention. For more information, refer to the following article in the Microsoft Knowledge Base:
Q151596 : INF: 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 passes the function a SRVPROC structure. After the DLL has the SRVPROC structure, you can perform standard Open Data Services operations to obtain parameters and return results to the caller.

Remember, as a DLL, it is loaded in the address space of calling process. In the case of the extended stored procedure, the process is SQL Server. If a DLL is improperly accessing memory or is not thread safe, you can adversely affect the process. Thorough testing must be done to ensure that the DLL maintains the integrity of the process. If there is any concern that an extended stored procedure may be adversely effecting SQL Server, you should address it immediately.

For example, you can use the Visual C/C++ wizards to create a DevStudio Add-in Wizard. This wizard is an In-Process COM server, or DLL. If your wizard is not properly written, it can adversely affect the process.



For example, suppose you had the following: 

   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 strncpy states that it will copy the second string into the strName and then 0 fill the rest of the buffer. Thus the example is writing 35 bytes, even when the second string is 3 bytes in length. The strncpy most likely will not cause an access violation because you are still within the process address space. However, the operation could have easily corrupted an internal memory structure, leading to unexpected process behavior. In the case of the SQL Server process, a mistake of this nature might corrupt a critical internal SQL Server structure and, as such, could manifest itself dropped connections or other unexpected SQL Server behavior. Additionally, the server may stop responding.

SQL Server attempts 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. A key point to remember is that the protection is provided with a try/except block and not a try/catch block. Therefore, the code will not perform stack unwinding for objects.

Memory Leaks

Any project may have a bug where allocated memory, a handle, or similar resource is not being released properly. It is paramount to any DLL test suite that the suite ensures that the DLL is releasing all resources correctly. These types of issues are likely to manifest themselves as increased page file usage, altered performance, or increased paging.

Thread Safety

Applications like Microsoft Internet Information Server (IIS) and SQL Server are thread pooling, multi-threaded applications. This means that your DLL can be invoked from multiple connections at the same time, especially on a computer with multiple processors. It 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.

Ensure that all code paths are thread-safe and reentrant. Link with multi- threaded runtime libraries, and make sure all vendor DLLs you are using are thread-safe as well. For complete details on Thread Local Storage and a detailed account of thread safety issues, consult the following article in the Microsoft Knowledge Base:
Q163449 : INF: Use of Thread Local Storage in an Ext. Stored Procedure

Structure Exception Handling

You should also have a clear understanding of structured exception error handling. Every entry point in a DLL should properly account for exception errors. SQL Server attempts to catch exception errors but any DLL should 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 it exception naked. If the thread does not install a try/except or try/catch block immediately, the thread is only protected by the operating system. ANY exception error encountered by the thread is considered unhanded and FATAL to the entire 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 you develop should 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 SQL Server. These are described in the xp_dblib and xp_odbc samples, which come with the SQL Server Programmer's Toolkit.

Loopback connections can only be performed on bound sessions. One problem with a loopback connection is that it is a new connection and therefore is in a separate transaction space. For example, suppose the extended stored procedure performs a complex mathematical calculation on the sales table. The loopback connection attempts to complete a SELECT operation on the sales table. However, the original connection had performed an UPDATE to the sales table. Unless diligent care has been taken to implement a query timeout, asynchronous query processing and SRV_GOTATTENTION is being checked, 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.

Please remember, SQL Server only supports loopback connections on bound sessions.

When dealing with a blocking issue, refer to the following articles in the Microsoft Knowledge Base:
Q162361 : INF: Understanding and Resolving SQL Server Blocking Problems

Q180775 : INF: 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 SQL Server or an Open Data Services gateway is 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 should 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.

Also, refer to the following article, which explains the limitation of DB-Library usage in an extended environment:
Q174817 : Microsoft SQL Server DB-Library Has Limited Extensibility



The Open Data Services API call srv_message_handler allows you to place text in the SQL Server errorlog. For more information, consult the following article in the Microsoft Knowledge Base:
Q164290 : 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 should not be called from a DLL because of the effects to applications like 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 should check for SRV_GOTATTENTION so it can handle requests from the client to cancel the operation. However, the SA is currently not able to issue a Transact-SQL KILL statement to interrupt the execution of an extended stored procedure. This makes it all that 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 should 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; ensure that the DLL does not use these calls.

Srv_Senddone

By default, SQL Server will automatically call srv_senddone with the SRV_DONE_FINAL flag on return from the invocation of an extended stored procedure. The extended stored procedure should NOT call srv_senddone with SRV_DONE_FINAL; instead it should use SRV_DONE_MORE.

String Termination

When dealing with strings returned from the Open Data Services API, you should always ensure termination. A string returned from srv_paramdata is not guaranteed to be NULL terminated. You must use the srv_paramlen to properly manipulate the strings. Other Open Data Services functions may be similar; test them thoroughly.

Additional query words: xproc xprocs sproc sprocs st proc procs ODS
dblib tsql transql 


Keywords          : kbprg SSrvProg 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: April 13, 1999