| Author |
Topic |
|
yz
Starting Member
16 Posts |
 Posted - 2005-03-17 : 12:08:11
|
| Hi,I would like to know if a MS SQL server can somehow notify its clients when an event happens such as insert/update/delete a record from a table. I know on the server side we can use trig to get these events and add code to excute some stored procedures. However, I do not know how to use it to nodify a client. How can a client get such a notice and do whatever query accordingly in real time? Right now my client program is polling from the server to get the data from every table every second. That is not a good solution.If anyone knows the solution and post a suggestion here I would greatly appreciate it.YZ |
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-17 : 12:10:52
|
| BTW, my client program is written in C# using ADO.Net. Thank you in advance |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-17 : 12:23:43
|
one option is to mail the client, which isn't good if you have many clients.i don't know what you expect. server doesn't know which client is talking to himespecially over the web. so to do this you need a request to the server every few seconds/minutesto know the real condition.maybe you should explain why do you want this and we can present some alternatives.Go with the flow & have fun! Else fight the flow  |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-17 : 14:04:13
|
| Thank you Spirit1.I have a few clients in our LAN, not for people on web(The web option may be requested in future, not now). No more than 5 of them at any given time.Each of the client programs is monitoring a few tables' record change in the server on the real time bases 24/7. If data in some records meet some limits, a client will send a command to update a command table in the server. The server side of program will monitor this command table in real time and take some actions to control a group of machines accordingly. If we had 2 seconds delay in this communication chain anywhere, we would have high chance to damage the $100 K machine. Timing is very critical here.Hope I state my case clearly here. |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-17 : 15:33:39
|
| A little more info here.In client side, I can get an event fired if the client program is updateing table itself and use SqlDataAdaptor's SqlRowUpdatedEventHandler. However, it does not not do me any good since the data in the tables are updated primarily by the server program itself and other clients. Unless the server program can send the client a note, SqlRowUpdatedEventHandler would not be called. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-18 : 07:16:07
|
well if 2 sec delay can be fatal then you've got a hard scenario on your hands  1. use sprocs for all data access.2. create a new table that recevies the limit notification from triggers on observed tables (you put a trigger and insert some data into a new table that tells you if the table is "full" or something like that)3. in all your querries add a join to this table with some where conditions. that way you have instant info if the table is full or not.you'll have to figure out the full logic by yourself. instant notifications from server to client are very hard/impossible to implement in a simple manner.Go with the flow & have fun! Else fight the flow |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-18 : 07:49:37
|
| Put in triggers:exec master..xp_cmdshell 'NET SEND ...'or (even better):RAISERROR ... & ALERT hooked on the user-defined error. |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-18 : 12:28:02
|
| Sprit1 and Staod,Thank you two very much for your comments.I looked at Trigger earlier. Yes, I can certainly get the event fired when inser/update/delete a record by using Trigger. However, the question is once I get it, on the server side of the procedure, what can I do to send a message to the clients? I did not think about using "net send" even it is a simple and good broadcast tool because I do not know how to receive the meaasge from the client side of the C# program. I will look into it.The other option I am thinking is to establish a TCP/IP client/server communication between the DB server application and the client application programs, the DB server application can easily broadcast the insert/update/delete record events to all clients. The message itself can be very concise. Upon receiving the message, the client programs can take action accordingly . This way the client program may have better performance than polling since it is asynchronous event driven application instead of being controlled by timer. I have exiting working code for TCP/IP client/server communication so implementing it will be easy. How do you think? Any other suggestions? |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-19 : 22:33:35
|
| yz,(I must admit I didn't understand properly what do you mean by "the clients"),and, of course, only you know all details of the whole thing... nevertheless:1.Why can't you update Command table directly from triggers of the other tableswhich are polled (on Timer basis) by the apps located on different LAN machines?2.If it's an absolute necessity, why not just fire remotely those apps from triggersinstead of using CLIENT/SERVER/TCP/IP stuff which can turn out to be painful? |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-03-20 : 07:29:41
|
| trigger calling xp_cmdshell 'net send' was my first thought as well, but if it's a web app you could have SQL Server push the info to front-end clients through the middleware too. See sp_makewebtask and FOR XML in SQL BOL. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-20 : 13:06:24
|
| Crito,it's a pure real-time LAN thing. No web and no http. |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-03-20 : 17:12:24
|
| Well, I've used DDE with a custom written service to do something similar back in my Windows 3.1 days. Clients would connect to service to get real-time status info pushed to them, but only the service would query SQL Server (v4.21), minimizing locking on system tables. Might be able to do something similar with sample C# code for an IRC two-tier client/server app... using a custom service to push status messages to your clients using IRC protocols instead, that is. Might even be able to implement it as a DLL and extended stored proc, now that I think about it some more. ;) |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-21 : 19:21:31
|
| Stoad,Thank you for your questions. Here are my answers:1.Why can't you update Command table directly from triggers of the other tableswhich are polled (on Timer basis) by the apps located on different LAN machines?The most of time when needs urgent operation, the machines makes real time decisions inside themself before the data even reach a database. However, there are rare cases operators have to make decision based on the DB data that machines think OK but human think no. So a command from an operetor must be able to excuted.I do not want to use the timer based apps to poll data. That is where this post came from.2.If it's an absolute necessity, why not just fire remotely those apps from triggersinstead of using CLIENT/SERVER/TCP/IP stuff which can turn out to be painful?I like to fire remotely those apps from triggers but do not know how. Can you suggest any way to implement it? Trigger calling xp_cmdshell 'net send' has a practical problem for me as I stated above.Crito, Thank you to join our discussion too. Adding an IRC two-tier client/server module is the suggestion that I had above. It is doable but could be painful as Stoad pointed out. I wish I could use other way if possible. DDE or OLE automation can pass data between applications in a same machine in the old window world. I do not know what is their alternative today for .Net applications and how to pass event/data between different applications on machines in a LAN other than another client/server module. Any suggestions on this matter would be greatly appreciated. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-22 : 04:31:13
|
| > I like to fire remotely those apps from triggers but do not know how.> Can you suggest any way to implement it?I'd suggest to use tiny PsExec (only 50 KB), very reliable and elaborated tool:http://www.sysinternals.com/ntw2k/freeware/psexec.shtmlexec master..xp_cmdshell'psexec \\server -i -d -u administrator -p pwd C:\winnt\notepad D:\my.txt', no_output??? |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-22 : 13:31:08
|
| Stoad,Thank you for your suggestion. I think I need to explain my case more since PsExec is not a workable solution for me. My client/DB reader application is running 24/7 to monitor DB data. PsExec, on the other hand, launchs an application. So the application launched by PsExec will have to find a way to send data to my client/DB reader application. The solution will add the application launched by PsExec and makes the whole thing complicated.I did a little more search for Microsof's alternative to its old OLE/COM/DCOM technology. Well, they introduced ".NET Remoting" nowadays..NET Remoting is a powerful way to enable interprocess communication on a LAN. It is more complicated to program against than Web services. It can exchange real time data between different .Net applications on different machines. Since my client/DB reader application is a .Net application, it is a good candidate for using .NET Remoting.If I use .NET Remoting technique, the question will be:"Can a trigger in MS SQL Server call a C# or VB.net object directly without calling another application"? If the answer is Yes, I hope I can find an exmaple.Thank again for your help and discussion. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-22 : 13:34:52
|
| look into a sp_OA* sproc family. they might prove usefull...Go with the flow & have fun! Else fight the flow |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-22 : 14:38:44
|
| Yes... sp_OA* family...BUT,honestly speaking, I don't see a big difference between a FIRED trigger interacting directly with already running remote appsANDa FIRED trigger which FIRES an ancillary app (say, on the server side) which starts interacting with those running client apps. In fact, we can count this ancillary app as a continuation of the trigger's code. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-22 : 14:49:34
|
| yz let me clear something up...you said you have a client utilty (up to 5 at a time) that monitors some tables on a server?so you must send requests to the server that monitors the condition change.getting the message back to the client at this point doesn't seem hard to me. i already suggested a solution that might work for you.then you say that when this condition change happens you need the server to do some stuff on some machines.which machines? client machines (that monitor the data) or some other machines?Go with the flow & have fun! Else fight the flow |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-22 : 19:27:49
|
| sprit1Yes I have a client utilty (up to 5 at a time) that monitors some tables on a server. "so you must send requests to the server that monitors the condition change."Yes I do and I can get the new records back anytime I want to. However, although the client utilty application needs to get the latest records from the tables that are inserted into the table every half a second or every hour by another application, I do not want the client application send requests to the server every half a second unless it knows a new record is inserted into one of the tables. Bottomline, I do not want to user timer and poll the new records at a fixed time interval. I like to have an event driven client application that only requests data from the server when it knows a new record has come."getting the message back to the client at this point doesn't seem hard to me. i already suggested a solution that might work for you."I am not sure your solution works since you did not seem to understand my question, which is my fault for my poor English."then you say that when this condition change happens you need the server to do some stuff on some machines.which machines? "The "machines" here are not computers. They are power generators. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-23 : 03:28:06
|
| not sure if i'm oversimplifying this.we have a setup wherein any changes made on tables are being edited through triggers.the trigger checks which should be logged and send email to recipients.a job then checks the logged data and resends emails that were not sent. i guess the logic will be the same, checking an audit table then invoking a program?--------------------keeping it simple... |
|
|
|
yz
Starting Member
16 Posts |
Posted - 2005-03-23 : 10:58:28
|
| jen,Your logic is exactly what I wanted to start with. It is a simple matter. The difference between yours and my applications is that when we get a trigger in DB server, you are using email notification while I want to have an event trigged in my Win form C# client program. Then the C# client program knows when and where to get the new data. Getting a record from a table is easy. Get an event should be easy too but I have not figure dout how to send and receive a message after I get a trigger in server side. Email or an IRC two-tier client/server channel can be options but I hope to use something simpler. keeping it simple... I like it. Thank you. |
|
|
|
Next Page
|
Get notified from server when update/insert