Extended Stored Procedures | Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

It was impossible to implement certain SQL Server features through Transact-SQL statements. The designers of earlier versions of SQL Server have developed a way to use the functionality encapsulated in special DLL libraries written in languages such as C or C++. Extended stored procedures are actually these C functions encapsulated in DLL files. They have a wrapper stored in the master database that uses the prefix xp_. Using this wrapper, you can access them just as you would any other stored procedure.

Note

Selected extended stored procedures stored in the master database are named with the prefix sp_ to allow users to access them from any database (such as sp_execute, sp_executesql, and sp_sdidebug).

In the following example, the extended stored procedure runs an operating system command to list all scripts in the BINN directory. Since it is not declared with the sp_prefix, you must qualify its name with that of the database in which it is located:

      Exec master.dbo.xp_cmdshell 'dir c:\mssql\binn\*.sql'

SQL Server stores DLL files of its own stored procedures in the C:\Program Files\ Microsoft SQL Server\MSSQL.x\MSSQL\Binn folder.

Tip

Since SQL Server 2005 supports the development of stored procedures in .NET languages, Microsoft has announced that they will drop support for extended procedures in future versions. Use .NET stored procedures instead of extended stored procedures because they will be safer and more scalable.

Design of Extended Stored Procedures

It is not possible to create an extended stored procedure from just any DLL file. The file must be prepared in a special way.

The development of extended stored procedures is based on the use of the Open Data Services API (ODS API). In the past, it was a tedious job and the developer had to perform all tasks manually. Nowadays, the process is automated in the Enterprise Edition of Visual C++ through the Extended Stored Proc Wizard. I will quickly demonstrate its use.

With the proper initialization code, the Extended Stored Proc Wizard generates Win32 DLL projects that contain an exported function. You should change the content of the exported function to perform the job of the future extended stored procedure. The wizard includes the header file (srv.h) and a library (opends60.1ib) needed for using ODS in the code.

To create an extended stored procedure:

In Visual C++ Enterprise Edition, select File | New. The New dialog box should appear with the Projects tab opened. You need to set the name of the project. You could and should also use the name of the extended stored procedure as the name of the project. Extended stored procedure names commonly begin with the xp_ prefix.
Select Extended Stored Proc Wizard from the list of project types:
When you click OK, the program will lunch the Extended Stored Proc Wizard It prompts you extended stored procedure:
Click Finish. The wizard generates the following project files:
- proc.cpp The exported Win32 function, which is the extended stored procedure
- [projnamej.dsp The Visual C++ project file
- [projnamej.cpp A file that includes DLL initialization code
- StdAfx.h An include file for standard system include files, or project-specific include files that are used frequently
- StdAfx.cpp A source file that includes just the standard includes
Open proc.cpp and change the code to implement features of the extended stored procedure. Figure 7-2 shows Visual Studio with the code of the extended stored procedure.
Compile the generated project to generate a DLL—[projname].DLL.

image from book
Figure 7-2: Code of extended stored procedure

The following code listing shows the contents of proc.cpp. It contains the exported Win32 function xp_hello. The function was generated by the wizard and it returns a simple message and a recordset that contains three records.

      #include <stdafx.h>      #define XP_NOERROR                  0      #define XP_ERROR                    1      #define MAXCOLNAME                  25      #define MAXNAME                     25      #define MAXTEXT                     255      #ifdef __cplusplus      extern "C" {      #endif       RETCODE __declspec(dllexport) xp_hello(SRV_PROC *srvproc);      #ifdef __cplusplus      }      #endif      RETCODE __declspec(dllexport) xp_hello(SRV_PROC *srvproc)      {          DBSMALLINT i = 0;          DBCHAR colname[MAXCOLNAME];          DBCHAR spName[MAXNAME];          DBCHAR spText[MAXTEXT];          // Name of this procedure          wsprintf(spName, "xp_hello");          //Send a text message          wsprintf(spText, "%s Sample Extended Stored Procedure", spName);          srv_sendmsg(              srvproc,              SRV_MSG_INFO,              0,              (DBTINYINT)0,              (DBTINYINT)0,              NULL,              0,              0,              spText,              SRV_NULLTERM);          //Set up the column names          wsprintf(colname, "ID");          srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVINT2,                       sizeof(DBSMALLINT), SRVINT2, sizeof(DBSMALLINT), 0);          wsprintf(colname, "spName");          srv_describe(srvproc, 2, colname, SRV_NULLTERM, SRVCHAR, MAXNAME,                       SRVCHAR, 0, NULL);          wsprintf(colname, "Text");          srv_describe(srvproc, 3, colname, SRV_NULLTERM, SRVCHAR, MAXTEXT,                       SRVCHAR, 0, NULL);          // Update field 2 "spName", same value for all rows          srv_setcoldata(srvproc, 2, spName);          srv_setcollen(srvproc, 2, strlen(spName)};          // Send multiple rows of data          for (i = 0; i < 3; i++) {              // Update field 1 "ID"              srv_setcoldata(srvproc, 1, &i);              // Update field 3 "Text"              wsprintf(spText,      "%d) Sample rowset generated by the %s extended stored procedure", i,       spName);             srv_setcoldata(srvproc, 3, spText);             srv_setcollen(srvproc, 3, strlen(spText)};             // Send the entire row             srv_sendrow(srvproc);          }          // Now return the number of rows processed          srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT,                         (DBUSMALLINT)0, (DBINT)i);          return XP_NOERROR ;      }

Tip

If you are fluent enougsh in the techniques required to create extended stored procedures, you should not he spending your time creating business applications. You should he working on more fundamental stuff like operating systems or RDBMSs and devoting your time to hacking. Let the rest of us collect the easy money.

Registering the Extended Stored Procedure

Once the DLL is compiled, the extended stored procedure has to be registered on the server before it can be used:

Copy the xp_hello.dll file to the SQL Server…\Binn folder.
Register the new extended stored procedure by using the SQL Server Enterprise Manager or by executing the following SQL command:
```
      sp_addextendedproc 'xp_hello', 'XP_HELLO.DLL' 
```

Once the extended stored procedure is registered, you can test it by using Management Studio. See Figure 7-3.

image from book
Figure 7-3: Using the extended stored procedure

You should carefully test the new extended stored procedure. If you find out that it is not working as expected or that you need to make some modification, you need to unregister (drop) the extended stored procedure by using the following SQL command:

      sp_dropextendedproc 'xp_hello'

When the extended stored procedure is executed in SQL Server, it is loaded into memory. It stays there until SQL Server is shut down or until you issue a command to remove it from memory:

      DBCC xp_hello(FREE)

To register an extended stored procedure from Management Studio, right-click the Extended Stored Procedures node in the master database (Databases | System Databases | master | Programmability | Extended Stored Procedures) and select New Extended Stored Procedure. Management Studio prompts you for the name of the extended stored procedure and the location of the DLL file:

image from book

It is also simple to remove an extended stored procedure using Enterprise Manager. You merely right-click it and select Delete from the pop-up menu.

Note

The trouble with extended stored procedures is that they work in the address space of SQL Server. Therefore, an extended stored procedure that doesn't behave properly could crash SQL Server. Such a problem is not likely to occur because SQL Server monitors the behavior of extended stored procedures. If an extended stored procedure attempts to reference memory outside of its address space, SQL Server will terminate it. Commonsense programming practices (using error checking, doing exception handling, and thoroughly testing final code) will further reduce the possibility of errors.