Ken Bergmann
Microsoft Developer Network Technology Group
April 1996
This article illustrates some ways in which Microsoft® SQL Server can be used in conjunction with the Microsoft SQL Server services to provide powerful functionality to a client/server system. This article assumes knowledge of the SQL-Data Maintenance Objects (SQL-DMO) object model, Microsoft SQL Server services, and client/server development.
As this article is part of a larger series on developing Client/Server solutions, it is recommended that the following articles in the series be read as background information:
Client/Server Solutions: The Architecture Process
Client/Server Solutions: The Design Process
Client/Server Solutions: The Basics
Client/Server Solutions: Coding Guidelines
In addition to being the fastest transaction processing database server in production today, Microsoft® SQL Server can provide many other services. These range from the SQL-Data Maintenance Objects (SQL-DMO), which manipulate the database system through OLE Automation, to SQL Mail, which enables connectivity between your data and your MAPI-compliant mail system.
This article will introduce you to some of SQL Server's new services and show you how to get more mileage out of your current solutions and create new ones effectively.
There are many features of Microsoft SQL Server services that will make managing a client/server system easier. In some situations, the everyday processing of a system may be easier to create by building around these services and objects instead of providing custom processing. An example is using the Task objects to execute batch processors instead of the AT command line or similar schedulers. By using these objects instead of SQL or command line interfaces, you can use the functionality of the built-in Event Log to track success and failure, or you can use the pager and e-mail notification processes that are also built into Microsoft SQL Server. Neither of these services require the developer to provide much, if anything.
To make use of alerts, tasks, and the notification structure, you need an understanding of SQL Executive. SQL Executive is a service for Microsoft Windows NT® that is provided along with Microsoft SQL Server. It has three basic jobs that offer lots of functionality:
SQL Executive is required for replication services, but we will be focusing on its other aspects. We can make use of SQL Executive because it is like a constantly cycling process running on the server. It is always interrogating the server and keeping track of the various aspects of both Microsoft SQL Server and the operating system. This aspect of its functionality can provide a lot of features with minimal effort.
For example, because of the cyclical nature of SQL Executive, you can have queries that are executed at any interval you want by using tasks. You don't have to program the interface or the "Keep Alive" smarts or anything! Just plug in the T-SQL or BAT or EXE and tell SQL Executive at what intervals to invoke the task. Voila! It happens all by itself. And when the job is executed, SQL Executive logs the start and stop times and all the errors and/or messages to the service log as well. This is great for those system operators who need remote processes to be run at certain times, but want tight control.
Tasks are fantastic for systems that have batch processing; alerts are great for systems that run unattended. Put together, these features can drastically improve the design even of simple systems by providing batch processing and call-back functionality while requiring very little effort from the system itself. For example, imagine a simple data entry system for expense reports. You have this great database that manages the expense reports for each of the employees in your department. It has a small, fast interface that looks something like the following:
Figure 1. Current transition interface for a theoretical system
Now you need to be able to interface your cool transaction system with the monstrosity they call the budget database. The goal is for each transaction to be validated against the current budget and for each of the items on the expense report to be validated and subtracted from the current budget, depending on the type of transaction and the budget that each item falls under. Pretty straightforward, right? The only problem you can see is that inserting each item from an expense report into the various categories can have serious implications for the rest of the budget and reporting processes. Because changing anything in the budget database has such big repercussions, the process for doing this type of validation and merging can take quite a while.
Figure 2. Proposed transaction interface for a theoretical system
While this may seem like a normal situation, it unfortunately isn't one of the easier problems to solve. You could spend serious time trying to streamline the budget operations, you could try upgrading your hardware, you could try lots of things to make the transactions complete in less time. In the meantime, while you are working on getting the owners of the budget database to optimize their procedures so that you can release a usable product, the entire department must go without this much-needed functionality. Of course, after you spend so much time on it, there is no guarantee that any optimization you end up with will make the system usable. This is where SQL Services come in.
Instead of changing your current transaction model to facilitate extremely bulky transactions, you simply add two "hooks" to the current model. Hooks are like interfaces. They provide a means of talking about the linkages between systems that are not formally linked. You add one hook to the end of the current insert process. You might add the other hook as an OLE callback method or provide it in the form of the receipt of an e-mail message. The specific type of hook or callback is unimportant, and I'll go into more detail about this callback later. The key here is to design your system such that there are transactions into which hooks can be placed. From the previous illustrations, we can pick out two places for the initial hooks:
In either case, the first step might be to store incoming and deleted data in a queue table in one database or the other. Which database the queue resides in will probably be driven only by which database has more free time. I'll make the assumption that the expense report database has more cycles to spare and put the queue and the task there. The second step is to invoke the task. Since tasks run asynchronously, the hook would be completed at that point and the client could continue while the task ran on the server.
The hook that we are inserting is probably a wrapper of some kind for many transactions that will move the data through the budget database. In most cases, all of this would probably be done in stored procedures to encapsulate the logic and make it run fast. It would be very simple to have the last step in the wrapper-stored procedure run a Microsoft SQL Server task. Doing that is trivial in SQL and would look like this:
sp_runtask 'TASK NAMED FOO'
As an alternative, the task could be kicked off from within the application as well. Working with the SQL-DMO is fairly simple, and this is how invoking the task might be implemented in Microsoft Visual Basic® code:
Dim oSQLServer As New SQLOLE.SQLServer
Set oSQLServer = New SQLOLE.SQLServer
oSQLServer.Connect "MyServer", "MyLogin", "MyPassword"
oSQLServer.Executive.Tasks("Task Named Foo").Invoke
oSQLServer.Disconnect
oSQLServer.Close
Notice that even in this extreme scenario, the impact to the front-end code is minimal and straightforward. This is the main strength of a hook—it requires only a very small hole. The optimal case in this scenario requires no front-end changes at all, only changes to existing procedures.
As the task executes, SQL Executive will log start and stop times as well as any error conditions and, if required, will notify an operator through pagers, e-mail, or network messages. Upon completion, the task invoked in the first hook has many options for providing a callback to the client.
The callback hook can be implemented in any number of ways. The next section will lay out some alternative ways to implement a system-level callback in this context.
One option is that a callback is generated in the form of an e-mail message to the user with the results of the operations. Using Microsoft SQL Server Enterprise Manager, this tremendous feature is fairly easy to implement and could provide a great deal of information. Here is an example of how to send e-mail from within a Transact-SQL (T-SQL) statement:
xp_sendmail @recipients = 'sysadmin;acctreq', @copy_recipients = 'netops;msgops',@subject = 'Task Completion Notification',@message = 'The task completed successfully.'-- or to send a resultset would bexp_sendmail @recipients = 'sysadmin',@query = 'select * from #logtable',@attach_results = 'TRUE',@subject = 'SQL Server Report',@message = 'The task completed successfully. Log included.'
You can find information on the specifics of connecting your Microsoft SQL Server to your mail client on the MSDN Library CD.
A more sophisticated callback takes the form of a stored procedure that is invoked at the end of the first task. This procedure then manipulates an object on the server or uses Distributed COM (DCOM) to create a reference to the client (which at this point is acting as an automation server) and execute a callback method on the client, providing any information needed. This is what the process looks like:
Figure 3. Process for executing callbacks on the client from the server
Here is what the extended procedure code might look like in the budget database:
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
-- Create an instance of the callback server
EXEC @hr = sp_OACreate 'VBOLEServer.CallbackClass', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Set the WhoIsCalling property
EXEC @hr = sp_OASetProperty @object, 'WhoIsCalling', 'ThisProcedureName'
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Call the NotifyClient method
EXEC @hr = sp_OAMethod @object, 'NotifyClient', 32, 'Completed', @return OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
IF @return <> 0
BEGIN
EXEC sp_SendAlert 'Failed To Notify Client. '
RETURN
END
-- Destroy the server object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
More information about manipulating OLE servers from Microsoft SQL Server can be found in SQL Server Books Online.
Still another option is to use the alert mechanisms to send notifications upon unsuccessful completion of the task. In many business processes, feedback should be provided only in the case of error conditions. It is for these types of situations that this option provides a cool alternative. Here is what the transaction process might look like on Microsoft SQL Server:
Figure 4. Transaction process on the server
In order for the alert to fire, it must be created on the server beforehand. It can be created at run time using stored procedures or SQL-DMO code, but this isn't recommended. As well as creating the alert in advance, you need to assign criteria for the alert so that SQL Executive knows when to fire the alert. The simplest way to set up the criteria is to use a custom Error Number, which you also set up in advance.
There are two primary ways to actually get the alert to fire from inside the task. The first is to simply do a RAISERROR WITH LOG using the appropriate error number. The more appropriate way is to use the xp_logevent procedure. The SQL code for these two methods might look like this:
RAISERROR (@CUSTOM_MSG_NUMBER,0,1) WITH LOG
-- or the preferred syntax while within a task
EXEC xp_logevent @CUSTOM_MSG_NUMBER, @CUSTOM_MSG_TEXT, informational
For more information on using Extended Stored Procedures or alerts, see the documentation in SQL Server Books Online.
What all this boils down to is that the requirements for a callback are based entirely on the way you choose to run your business. If e-mail notifications are commonplace and considered an acceptable form of "receipt," then you might choose a limited code option. If your business operations include the use of alerts or must be tied closely to one another, then an OLE server or task-based solution might be the option for you. Development time and complexity are certainly issues to keep in mind as well.
At the end of the day, there are many ways to leverage the services of Microsoft SQL Server, depending on the level of control and timeliness required. With these services, it is possible to add a whole new spectrum of functionality to your working systems with a minimum of intrusion simply by changing your current model slightly. The main benefit is that the openness of these new services means that you are not required to jeopardize your current and viable solution simply to add new features.