Q190987 INF: Extended Stored Procedures: What Everyone Should Know
-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft SQL Server version 6.5
(Although this KB was written for SQL Server version 6.5, many issues are
relevant to all versions of SQL Server.)
-------------------------------------------------------------------------------

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.

Additionally, certain Open Data Services (ODS) calls are designed solely for use
in an ODS-based application and should 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 installed by SQL
Server and may lead to unpredictable failure conditions.

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