How to use extended stored procedures (190987)

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:

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:

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:

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: 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.