Tutorial on Extended Stored Procedures for MS SQL Server v7.0

Download demo project - 296 Kb

Download source files - 11 Kb

Introduction

Open Data Services is an interface that can be used to build robust applications that extend the power of MS SQL Server.

Extended stored procedures

An extended stored procedure is a C or C++ DLL that can be called from Transact-SQL using the same syntax as calling a Transact-SQL stored procedure. Extended stored procedures are a way to extend the capabilities of Transact-SQL to include any resources or services available to Microsoft Win32 applications.

In other words you can create a Extended Stored Procedure in your custom language (VC++, VB, Delphi). These dll's can use API's provided by Open Data Services to interact, control and enhance the functionality of SQL server to provide the functionality you might require.

Examples:

1. Imagine you have an application that performs some processing on the basis of the some records inserted newly in a particular table. The common way that one may do the same is open a recordset connection on the table and keep polling and re-querying till a new entry is inserted.

   The drawback in the above example is polling, it would put a lot of burden on the table on frequently accessed tables. The Extended Stored Procedure would adopt an event based approach, where an extended stored procedure residing on the server would be triggered on a new insert and perform custom processing.

2. Imagine you have an application that needs to perform some checks on some condition and immediately send a response mail to someone notifying of the event.

   The drawback in the above example is you might have a particular condition to trigger the event today and a totally different condition tomorrow. The Extended Stored Procedure would adopt an event-based approach where your custom dll could generate and manage mail, all that one would have to do is to trigger the Extended Stored Procedure in the SQL Server trigger.

Some quick tips on installing an extended stored procedure.

Adding an extended stored procedure:

To add an extended stored procedure function in an extended stored procedure DLL, you must run the sp_addextendedproc system stored procedure, specifying the name of the function and the name of the DLL in which that function resides. The user executing the command has to be a SQL Server system administrator.

For example, this command registers the function xp_sample, located in xp_sample.dll, as a SQL Server extended stored procedure:

sp_addextendedproc 'xp_sample','xp_sample.dll'

It is a good practice to place the extended stored procedure dll in the SQL Server bin path along with any dependent dlls. SQL Server uses the LoadLibrary() method to locate the dll.

Removing an extended stored procedure:

sp_dropextendedproc 'xp_sample','xp_sample.dll'

How Extended Stored Procedures Work

When a client call (from ISQL, your own application) to the extended stored procedure is performed which in turn executes an extended stored procedure, the request is transmitted to the SQL Server. SQL Server then locates the DLL associated with the extended stored procedure, and loads the DLL if it is not already loaded using the LoadLibrary() method. So inherently the first call to the stored procedure will have the overhead of loading the dll. Then SQL server calls the exported extended stored procedure located in the dll, which gets an interface to the SQL Server in form of a Server Procedure. Your procedure can then read parameters passed to it and return back results if it so intends.

However if your extended stored procedure raises an exception its process is killed and you have to restart the SQL server. SQL server has to be restarted even if you intend to replace the XP dll as it may have been loaded.

Programming

The sample extended stored procedure provided xp_sample is compiled and tested in VC ver 6.0. It should work on version 5 too as it has been tested.

I have created the sample by first creating a MFC Regular DLL statically linked. (VC 6 provides XP's in the AppWizard but when I started working on a project 6 months back we had to use VC Ver 5.0). Don’t forget to include the Srv.h which has the declarations of the ODS functions and link to the library opends60.lib.

Then define your export Extended Stored Procedure function which is of the format:

SRVRETCODE xp_sample(SRV_PROC *srvproc)

Where srvproc is a handle to the client connection and SRVRETCODE indicates the success or failure code that you intend to return. Do not forget to include the function in the exports .DEF file. The sample first gets a count of the no of parameters passed to it using the srv_rpcparams function. Then it gets the length, type and data of each paramater using srv_paramlen, srv_paramtype, srv_paramdata functions respectively. It then appends all the data received and sends it back to the SQL Server using srv_describe, srv_sendrow and srv_senddone functions.

Programming Conventions

1. It is a recommended practice to name your extended stored procedure and your extended stored procedure dll to start with xp_ by convention.
2. It is also recommended that all Microsoft SQL Server extended stored procedure DLLs implement and export the following function:<>

   __declspec(dllexport) ULONG __GetXpVersion()
   {
      return ODS_VERSION;
   }
   

   It helps to prevent warnings and allows version checks when your xp is installed against the ODS Library on the SQL Server Installed as your stored procedure library may require a ODS version higher than what is installed on the SQL Server on which it is intended to be installed.

Debugging an Extended Stored Procedure

To debug an extended stored procedure DLL by using Microsoft Visual C++ follow the following steps.

1. Stop SQL Server.
2. Copy the updated SQL server extended to SQL Server bin directory preferably.
3. Register the SQL Server using the sp_addextendedproc system stored procedure.
4. In the project settings output Debug category for:
   • Executable for debug session: Provide $MSSSQLSERVERPATH$\BINN\sqlservr.exe
   • Working Directory: Provide $MSSSQLSERVERPATH$\BINN
   • Program arguments: -c so that SQL server starts as an application and not as an service.
5. You can then put your break points and debug as normal.

Note:

1. If you plan to use MFC in the stored procedure or any other dependencies be sure that it is statically linked or the dependency dlls are also available in the $MSSSQLSERVERPATH$\BINN path.
2. Your include directory in options must contain the SQL Development tools include and lib paths.
3. I have tried compiling and checking whether it works on SQL Server 6.5, it does. But I doubt Microsoft supports it for version 6.5.

Best of luck and happy extended stored procedures.