Professional SQL Server™ 2005 Programming

6.7. Using External Calls to Perform Complex Actions

We have always had the need, on occasion, to get information thatis sourced outside of SQL Server. For the vast, vast majority of installations, actually getting that information from within SQL Server was out of reach. Instead, there was typically a client or middle tier component that sorted outwhat was needed from SQL Server and what was needed from the external source.

In many ways, this was just fine — after all, having your databaseserver hung up waiting on an external call seems risky at best, and deadly atworst. Who knows how long before that call is going to return (if ever?). Therisk of hung processes within your database server winds up being fairly high.

Now, I said for the majority of installations, and thatimplies that a few got around it — and they did. There were a few differentmethods available.

First, there was the idea of an extended stored procedure. Theseare DLLs that have that you can create in C using special SQL Server libraries. They run in process with SQL Server and can be (assuming you have a smart DLLwriter) very fast, save for one problem — an example is an external call. Thatmeans that we are beholden to the external process we are calling to return tous in a timely fashion. The additional issue was one of general safety. Sinceyou're running in process to SQL Server, if your DLL crashes, then SQL Serveris going to crash (if you're distributing software, I'm sure you can guess athow your customer ...