Extended Stored Procedures deprecated in SQL Server 2008

The end of an era…kind of sad to see them go

Extended Stored Procedures were really cool in their day, and I’m sort of sad to see that they’re going away.  In SQL Server 6.5 and 7 if you recall these objects were the only way that you could create a function in SQL Server (save maybe an OLE automation procedure); remember that TSQL UDFs didn’t come along until SQL Server 2000. So, if you had some sort of mathematical procedure or a proprietary-type function you could create an “XPROC”, import it into the SQL namespace, and call your function as if it were similar to today’s CLR functions or TSQL UDFs.  I wrote one once that encrypted data as it was stored in SQL Server, very nice at the time, especially when the newest .Net features or built in encryption were nowhere in sight.  Another reason that I liked them was that, coming from a c and c++ background, I could put to use the knowledge that I learned in college courses to create these objects. Finally, I read somewhere, it was one of those SQL Server Magazine online polls I think, that only 8% of all DBA/database devs actually knew how to write one.

XPROCS were written in either c or c++, and could be described as plane-jane .dlls;  these weren’t COM or Active-x .dlls or anything like that, but rather just regular old functions that you wrote using certain guidelines and methods (using the ODS API) that SQL Server would recognize.  To add them to SQL Server, you dropped the .dll into the binn folder and then ran sp_addextendedproc to make them become a part of SQL Server.  Many times you might hear someone say something derogatory about them, for instance, such as they had to be written correctly or you could bring the server down.  And, this was pretty much the case. They had be written to fairly strict rules or you could completely jack up SQL Server; I had to reboot many times due to either overruns in memory or just bad coding.  However, writing them correctly gave you no problems whatsoever. After all, SQL Server itself used many of them and they worked well for years (remember xp_sendmail, sp_executesql, sp_OACreate, xp_cmdshell, et. al.?).  MSDN usually had examples, and those were the best to follow when rolling your own. 

Back in either 2000 or 2001, the late, great Ken Henderson gave a presentation at the North Texas SQL Users group which I attended, and I was fascinated at some of the things that he did with Extended Stored Procedures.  That presentation gave me a lot of ideas for their use in my own shop, and I got some great tips from him on writing them.  He was a great proponent back then for writing objects to enhance the relational engine using a procedural language, and I was fortunate to have had the opportunity to learn from him and ask him questions about writing XPROCS.

So, today we have CLR objects, a snap to write and deploy, especially compared to what we had years ago.  In comparison, these are no challenge in the least compared to the old XPROCS.  Extended Stored Procedures were almost an art to write, deploy, and debug, and I know that I’m better today for having fought with them to expand the delivered functionality of SQL Server. Who knows – maybe those who wrote them back in SQL Server 6.5, 7 and SQL Server 2000 served as a part of the call to have what we have today.

Lee

--------------------------------------

About the time that we learn something, they take it away from us…