Send message from SQL Server trigger

Question

I need to signal a running application (Windows service) when certain things happen in SQL Server (2005). Is there a possibility to send a message from a trigger to an external application on the same system?

Answer 1

You can send an email from a trigger, but it isn't a recommended practice becasue if the email ssystem is down, no data changes can be made to the table.

Personally if you can live with less than realtime, I would information about the event you are interested in to another table (so the real change of data can go smoothly even if email is down for some reason.) Then I would have a job that checks that table every 5-10 minutes for any new entries and emails those out.

Answer 2

Not sure DBA's would approve of this, but there is a way to run commands using xp_cmdshell

"Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell."

Example from MS's site:

CREATE PROC shutdown10
AS
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down 
   in 10 minutes. No more connections allowed.', no_output
EXEC xp_cmdshell 'net pause sqlserver'
WAITFOR DELAY '00:05:00'
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down 
   in 5 minutes.', no_output
WAITFOR DELAY '00:04:00'
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down 
   in 1 minute. Log off now.', no_output
WAITFOR DELAY '00:01:00'
EXEC xp_cmdshell 'net stop sqlserver', no_output

Answer 3

You can use a dbmail email message. It should not slow the trigger down if the mail server is down because the message is queued and then sent by and external (to sql) process.

The table idea sounds good if the application can access sql server.

You could also give access to that same table via sql 2005 native XML Services - which exposes procs through xml. http://msdn.microsoft.com/en-us/library/ms345123(SQL.90).aspx

Answer 4

Depending on what sort of message you want to send, you could use a CLR stored procedure to connect to a socket on the running process and write the message to that. If you have no control over the process (i.e. can't modify it) you could build a bridge or use a library that can issue a message in a suitable format.

For reliable delivery, you could do something that uses MSMQ to deliver the message.

Answer 5

You can use a SQL Service Broker queue to do what you want. The trigger can create a conversation and send a message on the queue. When it starts, the external process should connect to the database and issue a WAITFOR (RECEIVE) statement on this queue. It will receive the message when the trigger sends it.

Answer 6

A reminder that triggers can be problematic for stuff like this because they are embedded in set-operations. And being associated with tables, they aren't very sensitive to the context in which they are fired. The problem can be if they fire on an operation that involves multiple rows, because it's hard to avoid invoking as many instances of your action as there are records in the operation. Several hundred emails are not unlikely, for instance.

Hopefully "things that happen" can be detected in closer association with the context in which they happen (which also can be interesting to try to backtrack from a trigger.)

Answer 7

Either:

• Use RAISERROR (severity 10) to fire a SQL agent alert and job.

• Load a separate table that is polled periodically by a separate mail handling process. (as HLGEM suggested)

• Use a stored procedure to send the message and write to the table.

Each solution decouples the transactional trigger from a potentially long messaging call.