Another common application requirement is to execute an external command or executable file when the value of a field in a particular table reaches a certain level. An inventory application, for example, might reorder stock when the quantity on hand falls below a certain reorder point. The reordering process could involve an existing mainframe or minicomputer application instead of being entirely implemented on SQL Server.
With Open Data Services, you can put a trigger on the table in question. The trigger compares the quantity on hand with the reorder point. When the quantity on hand falls below the reorder point, the trigger issues a remote stored procedure call to an Open Data Services server application, passing it the part number as a parameter. The remote stored procedure event handler then automatically executes the mainframe or minicomputer transaction that you have defined in your application, using the part number as an input parameter.
The PROCSRV sample application provides a way to handle this type of SQL Server extension without any C programming. SQL Server can issue a remote stored procedure call to the PROCSRV sp_exec procedure. SQL Server passes the name of a command file or executable file on that server, along with any required input parameters. The sp_exec procedure automatically starts the requested command or executable file in response to the remote stored procedure event. SQL Server includes a similar extended stored procedure called xp_cmdshell.
Using this procedure, a SQL Server installation can draw on a set of simple command or executable files in any language to perform functions that are external to the SQL Server environment.