CLR Trigger -> Send Insert/Update/Delete Message to Windows Service

Hi,

I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.

My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.

Are there any other idea's how can i solve this?

Best regards,
Thomas

Hi,

what about using Query Notification ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I had the same problem as you and I have used two different tecniques: both of them uses CLR stored procs.

1. An OS event set by SqlServer: useful when to have to notify that an event occurred without any param. Each different event must be attached to a different event name.

[SqlProcedure]
public static void SetOsEvent(SqlString eventName) {
try {
EventWaitHandle.OpenExisting((string)eventName).Set();
}
catch (WaitHandleCannotBeOpenedException) {
}
}

2. An UDP Message. Useful for non-reliable and fast messaging system, such as broadcasting the internal net that an event has occurred.

[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message) {
try {
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
client.Send(datagram, datagram.Length, (string)address, (int)port);
}
catch{
}
}

[SqlProcedure]
public static void UdpBroadcast(SqlInt32 port, SqlString message) {
try {
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
System.Net.IPEndPoint ep = new System.Net.IPEndPoint(System.Net.IPAddress.Broadcast, (int)port);
client.Send(datagram, datagram.Length, ep);
}
catch {
}
}

Hope that helps.