|
Eddy Vluggen wrote: Why is there a commit, if you are only selecting records?
Just to be sure that he is committed to selecting records only. Nothing else! 
|
|
|
|
|
Not sure it belongs in this forum, I hope it does.
I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds.
How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome.
TIA
|
|
|
|
|
 What is the world coming to? Luc asking questions!!!!
I've done similar things in the past, but not using SQL Server.
I don't know what IPC capabilities are in your server or could be easily grafted onto it.
Given these two points, I'd say:
1. Make the server do it. Client to client gets way too messy when you consider clients appearing/disappearing on the fly. At least the server has a reasonably current idea of who's out there.
2. How I did it was to use sockets with the following simple protocol:
client sends requests
at most one outstanding
first request of a session is a logon
server sends responses to requests OR asynchronous notifications
any socket error => forced logoff
(therefore unexpected logon at server => kill old session, start new)
HTH
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Thanks Peter, yes I need to earn some Enquirer points too you know. 
I'm currently considering a UDP multicast, where producing clients just throw their "new critical data" message up in the air, and consuming clients react on it. I was hoping SQL Server itself would have some built-in mechanism, I need to read up on it I guess.
|
|
|
|
|
Sounds like your app is a little less demanding of accuracy. Mine was a multi-terminal raffle selling setup - server in the back room, one or two fixed PCs and some mobiles (J2ME phones with bluetooth-speaking battery-powered printers) wandering around amongst the patrons. Request/response were the obvious ticket sales (or not), logon/off, operator cash management, etc; the notifications were things like raffle closed, new raffle opening (i.e. clients, come and get the details)
I'd be tempted to write it up - there are some interesting design considerations - except that a lot of the code is Hall of Shame material. 
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
I don't mind reading an article with substandard code in it, as long as it says so while also offering valuable insight in concepts and/or design. And if the code really is horrible, we can always turn it into a "guess who wrote this code" competition in the appropriate forum...
|
|
|
|
|
I believe SQL Server has an event notification process that can inform a client of data change. That is as much as I know, thankfully I have never had to implement such a horrible scenario, to date I have always managed to talk the client out of this based on the cost and perceived (by me) fragility of the solution.
I have implemented a polling solution a long time ago (SQL Server 6.5) but was never really happy with the results.
[edit] have a look through Mika's[^] stuff I think he had some info on it at some stage [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Luc Pattyn wrote:
How would you implement this?
Depends on the full specs, sensei. If there were no additional concerns, I'd simply have an update-trigger log the primary key and a datestamp in a table, and have the clients poll the table's count every second. That would also enable you to limit the update-logging to specific fields.
Something like an RSS idea for SQL
Bastard Programmer from Hell 
|
|
|
|
|
Thanks Eddy. I was hoping for a solution that doesn't poll, something event driven. Maybe an SQL statement that actually blocks until a specified row/field gets modified or added (Yes I could spend an entire connection!).
|
|
|
|
|
My pleasure
An event upon data change still sounds like a trigger. Is CLR Integration on? You could write a trigger[^] in C#.
..and if you can spend a connection, you probably can spend a thread; monitoring a custom lightweight trace might be an option too.
Bastard Programmer from Hell
|
|
|
|
|
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A simple scenario would be: client1 is looking at a customers list; client2 (different PC, different user) is looking at the same list; client1 adds/modifies a customer record, client2 should get his display updated, or at least get a warning the data is stale. Of course polling can do that easily, I hope for an event driven solution though.
|
|
|
|
|
Luc Pattyn wrote: Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A trigger, traditionally, executes a piece of SQL when data is inserted/updated/deleted from a table. You can check *which* fields change in the trigger. MSDN has an example on a CLR Trigger[^] that uses pipes to communicate with the outside world. I'm not sure whether you can launch a socket from there, but once you can catch the change, you can inform your clients - one way or the other.
My client would then typically use the INotifyPropertyChanged interface to propagate changes.
Bastard Programmer from Hell
|
|
|
|
|
OK, I'll investigate how that works and fits with what I have already. Thanks again.
![Beer | [beer]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/beer.gif)
|
|
|
|
|
My pleasure, and thanks
|
|
|
|
|
I have to create a job that when a task is due the employee should get reminder notification 14 days before the due date and second notification before 3 days before the due date. how can i do this in SQL Server 2005
|
|
|
|
|
Create a job that checks the data store where the job is, compare the system date with the job due date, get the email address from the employee table and send the email using SQL mail.
The information about jobs can be found using sysobjects and the related sys% tables. You will need to do some research as to where it can be found. Employee information is your problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In table i have column that is type of varchar(n), and it's not primary/foreign key.
Now, what I need is SQL query (stored procedure) that will select all the records in that table like:
(pseudo)
select * from table1 where SUBSTRING(table1.column2,1,2)<@param1 and SUBSTRING(table1.column2,1,2)>@param2
Parameters are first two letters of any record that contains data for that column.
if param1 is aa and param2 is cc then output should be all the records with first two letters like:
aa,ab,ac,...,az,ba,..,bz,ca,cb,cc
Any suggestions would be appreciated!
|
|
|
|
|
First off, I think you have your signs backward.
Other than that, have you tried it?
|
|
|
|
|
Already solved and you're right, in solution that is similar to pseudo code I posted, the signs were backward... but it was just a hint for possible solution that someone could offer. Thanks for your comment anyway, cheers
|
|
|
|
|
select * from table1 where SUBSTRING(table1.column2,1,2)
like '%@param1%'
or SUBSTRING(table1.column2,1,2)>'%@param2%'
use dynamic query
|
|
|
|
|
Hi folks,
I have a database in MS SQL Server which is accessed by an application I wrote. In other words, I have control over both the DB and the software. Things have grown so much now that I'm getting worried I might not have set up all the correct indexes in the DB to really optimise the queries.
I should probably sit down and go through all of my code to see what queries are being run against the DB and what indexes would optimise those queries but there is such a multitude of queries and it would be very difficult to determine which of these are run frequently and which of them only spradically.
Is there a tool that can monitor all the queries that are being run against the DB over a period of time and then analyze the shortcomings in the DB design? Somehow I recall having heard of such a tool but I've never used anyhting of the sort and don't really know where to start looking.
|
|
|
|
|
From the SQL Server Management Studio, under the Tools menu, you will find "SQL Server Profiler".
Use this tool to see exactly what SQL statements are being run, then take those statements and run them in the SQL Server Manager, and under the Query menu click on the "Include Actual Execution Plan".
After running the query it will show you whether or not an index is used, which tables (or indexes) are being scanned, etc.
Cool stuff.
Good luck.
|
|
|
|
|
Thanks a bunch, that looks like exactly the thing I was looking for. The reason I couldn't find it was because, on my machine, I have a SQL express version of the Management Studio from which the Profiler seems to be missing. When I work directly on the machine that runs SQL Server, it is there.
Now I need to figure out how to set up a trace and how to make it meaningful. But thanks, I've got something to work from now.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
