locked
Best way to implement data-watch and sending message RRS feed

  • Question

  • User32934918 posted

    I am asked to develop/implement an application which queries a SQL table which has small set of real-time data, which typically changes by minute. Upon certain results of query, we want to send message to a specific computer on a network. What would be the best way to implement this?

    In prep, I have written SQL SP which looks at the table with real-time data and generate condition when message has to be sent. Also, I have read  MKB 321903 and have tested VB app described there in. The main architectural question I have is: I want to send message to some other machine on network (defined by hostname or IP) and not where the app is running. The small vb app I have written has messagebox.Show("...message..") but it can show only on computer where the app is going to run. I prefer to run app (or even just SP which has inifinite loop if you recomment) on the SQL server (with 2008 r2) machine with the ability to send message to another specific machine on network when sending message conditions are correct defined in my SP . Any idea/s?

    I have briefly looked into alerting system which can be set up for sending alert from SQL server but the more I read, the more it sounds like it is meant only to keep an eye on the SQL server health. Has any of you used this alert system to provide non-system custom alert described above?

     

    Thursday, June 4, 2015 2:34 PM

All replies

  • User71929859 posted

    You can use SignalR with SqlDependency as show in my below blog post

    http://ruchirac.blogspot.co.nz/2014/10/get-database-change-notification-in.html

    Friday, June 5, 2015 12:59 AM
  • User32934918 posted

    Hi Ruchira: I read through the link you had provided. Excellent Work! I might use it later when I am just looking at a database change. In the mean time, at this end, I had devided the task in two parts: 1. A SQL SP which does required calcs on real time data and saves calculated data every minute but keeps only latest 10 minutes data. and 2. Developed an Windows app using VS2010 which looks at the saved latest 10 minute data every minute and sends an alert if the thresholds are exceeded. Just finished implementing both and it is running great!

    I wish I could have been able to send message from the server to a specific machine (or user) of a desktop on intranet using msg.exe of Windows (which has replaced previous 'net send') but I could not (I could send a message to a server from a desktop (as the server can give if a particular user is logged on or not) but not from server to desktop (as it could not find sessions info when the request is made by msg - gives error).

    Thursday, June 11, 2015 5:02 PM
  • User71929859 posted

    I wish I could have been able to send message from the server to a specific machine (or user) of a desktop on intranet using msg.exe of Windows (which has replaced previous 'net send') but I could not (I could send a message to a server from a desktop (as the server can give if a particular user is logged on or not) but not from server to desktop (as it could not find sessions info when the request is made by msg - gives error).

    Hi,

    I don't think I understood what your requirement here. If you are talking about how to send messages to a windows client from server in real time, again you can use SignalR.

    http://ruchirac.blogspot.co.nz/2013/03/connecting-net-client-console.html

    Otherwise I feel it's out of the scope from asp.net.

    Friday, June 12, 2015 12:22 AM