Chapter 20. Extended Stored Procedures

for RuBoard

Programming in machine code is like eating with a toothpick. The bites are so small and the process so laborious that dinner takes forever.

Charles Petzold ^[1]

^[1] Petzold, Charles. Code . Redmond, WA: Microsoft Press, 2000. Page 349.

In addition to regular Transact-SQL-based stored procedures, you can also write stored procedures in C, C++, and other languages that can communicate with the ODS API. These types of procedures are known as extended stored procedures, and they receive parameters and return results via the ODS framework. They reside in DLLs and behave similarly to regular stored procedures. You create a reference to them in SQL Server by calling the sp_addextendedproc stored procedure (or through Enterprise Manager, which calls sp_addextendedproc). Sp_addextendedproc calls DBCC ADDEXTENDEDPROC() to add the reference to the sysobjects and syscomments system tables (sysobjects stores the object name, type, and so forth, as it does with all objects; syscomments stores the name of the DLL that hosts the extended procedure). This reference is the means by which you call the procedure from Transact-SQL. In this sense, it's very much like a regular stored procedure. From the perspective of SQL Server, extended procedures reside in the master database (they cannot be created in other databases) and run within the SQL Server process space.

There's nothing about extended procedures that requires them to be written in C or C++, but writing them in a different language would require you to first translate the ODS header files from C into your chosen language. I've written extended procedures in Delphi's Object Pascalso it can be donebut it's not for the timid.

If you want a stored procedure-like interface to routines written in a language other than C or C++ or for which you don't have source code, a reasonable alternative is to create a "wrapper" extended procedure using a C++ compiler, and call your routines (residing in a DLL or EXE) from it. This way, you leverage existing code and the language tools with which you prefer to work against the power of the ODS API. You get the best of both without having to translate code from one language to another.

Beyond the obvious language differences, there are some other important differences between extended procedures and regular procedures. Extended procedures aren't automatically located in the master database when called from other databases, and they don't assume the context of the current database when executed. To execute an extended procedure from a database other than the master, you must fully qualify the reference.

Another quirk I've discovered with extended procedures is that the DBCC command that unloads a DLL in which an extended procedure resides (DBCC dllname(FREE)) doesn't work consistently when the DLL initially resides in a folder other than the one containing sqlservr.exethe main SQL Server executable. I don't know why this is, but oftentimes the DLL will fail to unload if it was initially specified with a path . No error message is returned, but the DLL often remains loaded nonetheless. If you try to overwrite it, you'll find that it's still in use. The workaround, of course, is to copy the DLL to the SQL Server BINN folder (home of sqlservr .exe) and specify it to sp_addextendedproc without a path.

Internally, SQL Server uses the Windows API LoadLibrary() and FreeLibrary() routines to load and unload DLLs, and they certainly support fully qualified DLL filenames, so the path shouldn't be an issue. Nevertheless, I've seen the problem on several different systems, so it's something to watch out for.

for RuBoard