Calling Visual Basic .NET from a Trigger

Q: I need to call a Visual Basic .NET object when a specific event happens in SQL Server. I can capture this event through an INSERT trigger on a table, but how do I call the Visual Basic .NET object from the trigger?

A: You can solve your problem in several ways. Let's walk through the pros and cons of some different approaches. (See also, "Querying & Reporting: Reporting with Visual Basic 6.0" and "Visual Studio .NET FAQs.")

Option 1. Use xp_cmdshell to call out to the OS and run almost anything. For example, you could call an executable program that then calls whatever .NET program you need. However, you can't use this method to run a program that has a UI because a UI would try to write to the display on the SQL Server and wouldn't be able to, thus causing SQL Server to hang. Keep in mind that xp_cmdshell operates in a synchronous manner and doesn't return control to the SQL Server connection that called xp_cmdshell until the program that xp_cmdshell called completes. This could create problems within the trigger in several different ways. Most importantly, the synchronous nature of xp_cmdshell means the trigger might take much longer to finish, and the trigger will hold locks while it's running. These locks could block other connections, leading to serious contention problems on the server.

Option 2. Use sp_OA* functions to directly instantiate and interact with a COM object. Sp_OA* procedures have pretty high performance overhead, so I never like to use them for systems that handle heavy transaction loads. In fact, I tend to avoid them even on lightly loaded servers because transaction loads on servers often increase to volumes much greater than originally anticipated. Before going with this solution, research this topic further in SQL Server Books Online (BOL) and other resources to learn more about the possible performance effects.

Option 3. Use sp_start_job to start a SQL Server Agent job directly from the trigger. The SQL Server Agent job could include an ActiveX script task or a task that calls an OS-level command. The job could also call a Data Transformation Services (DTS) package, which in turn could manipulate your .NET program in any number of ways. Remember that SQL Server Agent jobs are single entry—only one person can run the job at a time. If an INSERT, UPDATE, or DELETE operation invokes a trigger to run a particular job while the job is already running, SQL Server generates an error message such as the following:

Server: Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Request to run job wait for me (from User NittanyBMoran) refused because the job is already running from a request by User NittanyBMoran.

So, starting a SQL Server Agent job directly from the trigger isn't a usable option in a multiuser environment.

You can use any of these techniques to call a .NET program (or any program without a UI) from a T-SQL trigger. However, I'm not fond of any of them because of the performance effects and limitations I've already mentioned.

Often when you need to call an external program, a few seconds' delay between when the triggering event happens and the external program is called is OK. If that's the case, I recommend using a trigger that writes some status information to a table that an external polling program monitors. The polling program could query the status table every few seconds (with no noticeable performance impact) and could call the .NET program when it identifies an event of interest. This approach offers stability and better performance than the first three options.