Run external programs from SQL Server stored procedures

• Download source files - 8.01 Kb

Introduction

In a real working environment, there are restrictions that prevent you from doing things "the way it should be done". For example, sometimes you cannot afford to buy the right software, user requirements are at times not clear or non-existent, you are not given the time to design the application properly, the existing hardware/software system does not support the leading edge technology you would like to use, etc. I believe as developers we are paid to be creative and we should always be willing/ready to improvise. As long as we are creative, we will never be replaced by robots that can follow the "perfect" but boring software development processes.

Today, I am going to introduce a SQL Server extended procedure that does something unusual: it runs other programs. Typically, this is not expected in real production systems and the DBAs will hate the idea. However, there may be situations where it will help you solve real production problems quickly. Suppose there is a complicated production system in your company that uses SQL Server database and it was written using technologies that became obsolete a few years ago. The system worked perfectly except that now you have a new business requirement: whenever a record that satisfies certain conditions is added to one of the database tables, something else (not supported by the old system) needs to happen. For example, you may need to call a web service or save/copy a file to an external system or send an e-mail message to the administrators. In other words, the thing you need to do may be simple (you can write a program that does this in less than an hour), but how to integrate your code into the old production system? It may be unrealistic or impossible (in terms of time and money) to make changes to the old production system. My extended procedure XYRunProc described below may be able to help you in such cases.

Let's say you need to call web service A when a record that satisfies certain conditions is inserted into table B.

1. First, you write a program that calls web service A.
2. Then, you modify the existing stored procedure that inserts rows into table B so that whenever a new row satisfies the predefined conditions the extended stored procedure XYRunProc will be called to invoke your new program.

Sounds like a simple solution, isn't it?

XYRunProc: A special SQL Server extended stored procedure

Unlike regular stored procedures, an extended stored procedure is implemented in a DLL that exports a function (the procedure). To install it, first, you need to copy the DLL into the MSSQL\BINN subfolder of the home folder for Microsoft SQL Server; then you can call the special procedure sp_addextendedproc supplying the names of your procedure and the DLL file. Extended stored procedures are installed in the master database. You can also install an extended stored procedure using the GUI interface provided by Microsoft SQL Server Enterprise Manager: just right click "Extended Stored Procedures" under the master database and select "New Extended Stored Procedure...".

Now, let me describe in detail my procedure XYRunProc, what it does, how to invoke it, and how it is implemented, etc.

XYRunProc can be called to run almost any external programs, it returns only one record. If you specify the command line, the working directory, and the input data, XYRunProc will create a new process with your command line and feed your input data to the standard input stream of the new process. The new process will be run with the working directory you provide. XYRunProc will also read the standard output stream of the new process and return it as a text field in the output record.

This extended stored procedure typically takes three parameters, an ASCII string as the command line for the new process, another ASCII string as the working directory, and an input data field whose type is not restricted by the stored procedure. For example, the SQL command below will invoke the HelloWorld.exe program in the folder c:\test\HelloWorld, the root folder c:\ will be the working directory of the new process, and the string Xiangyang will be used as the input data of the new process.

 exec XYRunProc 'c:\test\HelloWorld\HelloWorld.exe', 'c:\', 'Xiangyang'

If you provide null or empty string as the second parameter, then the working directory of the new process will be the same as that of the parent process (the SQL Server process). Note that only the command line (the first parameter) is required.

There are two more optional integer parameters, one is Timeout (the fourth parameter), and the other is Unicode (the fifth parameter). The Timeout value is the number of seconds the new process will run until it exits by itself or is killed by XYRunProc. If you provide null or zero as the Timeout value, then the default value 30 (seconds) will be used. The next parameter (Unicode) is a flag indicating whether the output from the program should be treated as Unicode text or not. Use value 1 if the output is Unicode. The default is 0 (ASCII, non-Unicode). As you can see, the standard output of the program you run with XYRunProc is restricted to text.

The procedure XYRunProc returns one record. The fields of the returned record consists of input parameters and a text field for the output from the new process. Here is an example (the returned record of the above SQL command):

The number of fields in the output record will vary depending on the number of input parameters you omitted (or supplied null as parameter values).

What if you want to run a program that does not read data from the standard input stream or does not write data to the standard output stream? In that case, the new process will still be created and it will be killed when timeout occurs. You can test this by running the following SQL command:

 exec XYRunProc 'notepad.exe'

You can run a whole batch of DOS commands using this procedure:

exec XYRunProc
'cmd.exe',
'c:\',
'cd test
cd HelloWorld
cd Debug
del *.*
Y
exit
'

The above SQL command will delete all files from the c:\test\HelloWorld\Debug folder. Note that you need to end each DOS command with a new line.

The implementation

There is nothing new or fancy in the technology that is used. Basically, I use functions from the libraries that come with Visual Studio .NET 2003.

• The srv_paraminfo function is used to get information on input parameters.
• The srv_describe function is used to define fields in the returned record.
• The srv_sendmsg function is used to send a database error message.
• The srv_sendrow function is used to output the returned record.

In addition, the CreateProcess function is used to create the new process and the _beginthread function is used to start a background thread that is responsible for terminating the new process when timeout occurs.

Risks and limitations

If you decide to use XYRunProc, you need to watch out for the following:

1. The process run by XYRunProc has system privilege. You must restrict the use of this procedure to trusted users. Otherwise, your database server will be taken over by strangers.
2. If your program has to call XYRunProc constantly, then you need to find a more efficient way to do the work. This is because each invocation of XYRunProc starts a new process which is a relatively expensive operation.
3. This procedure is best used to run programs that use standard input and output streams. As mentioned previously, data from the standard output stream should be text.

It is possible to modify XYRunProc to do things more efficiently. For example, instead of creating a new process, you can modify the procedure to do your work within the SQL Server process. The harder part is making the procedure flexible and easy to use.