SQL Server Extended Stored Procedures

The softwareAB team (led by Mr. Bruce) has written numerous utility libraries for software developers around the world. One of the more interesting (although difficult) aspects of software development is in developing customized extensions to a database. Within the Microsoft SQL Server 2000 database, these customized extensions can be written either using the database's built-in language (Transact/SQL, or T/SQL for short) or as external dynamic link libraries that can be invoked from T/SQL.

While using the built-in T/SQL database programming language to provide additional procedures and functions that can be called from a client application is the easiest solution, in some cases it's just not the best solution. And, depending on the type of 'extra' processing that your application needs to perform, T/SQL may simply not be powerful or flexible enough to accomplish your goals.

As a case in point, consider file transfers between the client and the server. Your application may want to access data files that (for scalability) are installed only on a shared server. However, your application wants to update its local file cache with the latest copy of a shared data file upon demand. Possible solutions include:

• Use a network share to access the data. However, this can be slow and is subject to security limitations of your network.

• Use a custom network transfer layer or Web service. This is a fine solution, but requires that you run software on the shared server that can satisfy the file request from the client.

• Use the database itself to store and transfer file data. This also is a fine solution, but suffers from the extra space required in the database.

Or, you could take a low-tech but highly reliable approach and have an Extended Stored Procedure installed on your database. Then you simply execute a stored procedure, and the stored procedure invokes your custom extended stored procedure. The extended stored procedure takes care of the details of reading data to or from the file server, and piping it back to your application as a standard resultset.

The advantage of the above solution is that it doesn't require any additional software to be running on the SQL Server box, since you're leveraging SQL Server itself to be your application server. Additionally, the SQL Server network data transfer (while not particularly speedy in this scenario) is highly reliable and almost guarantees that you won't have any strange technical support scenarios to work through.

The built-in T/SQL database programming language provided by SQL Server actually makes it pretty hard to gain access to the underlying file system directly, which is why we chose to use an Extended Stored Procedure to do the grunt work.

The Presentation

The presentation itself consists of the following items:

• SQL_Server_Extended_Procedures.ppt. This is the actual Microsoft PowerPoint presentation that highlights the steps involved in developing an Extended Stored Procedure.

• SQL_Server_Extended_Procedures.doc. This is the supporting documentation (basically, an Appendix) for the PowerPoint presentation.

• sql_server.h. This is the C++ header file that you can include in your own Extended Stored Procedure library to make accessing the SQL Server easy and safe.

• sql_server.cpp. This is the C++ source file that you can include in your own Extended Stored Procedure library to make accessing the SQL Server easy and safe. It implements the functions defined in the sql_server.h file.

Enjoy the presentation, and Happy Computing from all of us here at softwareAB.