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:
- 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.
- 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
- It is a recommended practice to name your extended stored procedure and your extended
stored procedure dll to start with
xp_
by convention.
- 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.
- Stop SQL Server.
- Copy the updated SQL server extended to SQL Server bin directory preferably.
- Register the SQL Server using the
sp_addextendedproc
system stored procedure.
- 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.
- You can then put your break points and debug as normal.
Note:
- 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.
- Your include directory in options must contain the SQL Development tools include and
lib paths.
- 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.