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.

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:

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

2. Select Extended Stored Proc Wizard from the list of project types:

   

3. When you click OK, the program will lunch the Extended Stored Proc Wizard It prompts you extended stored procedure:

   

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

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

6. Compile the generated project to generate a DLL— [projname].DLL.

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 ; }

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:

1. Copy the xp_hello.dll file to the SQL Server … \Binn folder.

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

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: