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:

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:

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