April 5, 2003 at 10:32 am
hello SQL expert,
i have an application running on more than one PC in the LAN and all of them connecting to SQL server 2000.
each application running on each PC might possible to insert new records into the table. I would like to create a GUI where can see the records just added by any PC.
Can i use the table's trigger function to inform my application new record is added. Otherwise, i need to put a timer object on my application to requery the new record and show on the GUI (Look similar like real time)
Thank you
April 5, 2003 at 12:07 pm
Interesting question. I don't think so, but maybe there is a creative way. You could fire xp_sendmail to send a notification to all the clients, they could "listen" for that and then either pull the info out of the email itself, or then poll the server for the new data. Maybe something possible with a dynamic cursor?
Andy
April 5, 2003 at 12:23 pm
Thanks for your reply AndyWarren.
The first way that you mentioned by using xp_sendmail , is it will cause more overhead of the system ?
i'm trying to use dynamic cursor, since currently i used VB's listview object to show online transaction record which is not data-bound control.
if using DBGrid to bound to a dynamic cursor (Using ADO) will it be showing all the new record automatically ??
Thanks
April 5, 2003 at 1:18 pm
I think you'd still have to do a refresh/requery (but honestly don't know for sure).
How many clients do you have and how quick do they need to know? Polling once a minute isn't going to kill you. Another way, what about having clients communicate with each other? COM+ supports publishing events across machines, or you could probably roll your own since you really just need a simple signaling mechanism. Could be as simple as connecting to the server to get list of active clients, then when you add a record you drop a file into a folder on each client machine (that way you're polling locally instead of the server), or you could use winsock to open a port and send a "new record" message.
Andy
April 5, 2003 at 1:54 pm
Thanks for your suggestion.
Actually, i have a bit confusing.
Currently, all my client directly connect to sql server to perform DB maintenance operation. I think it is not a good way since it causing a license issue. And i probably will create a server component which directly using only one connection object to sql server and all the client will share this connection to do the maintenance operation task, so that, if anyone client adding new record, the server component will be aware of it and send this new record to all this active client via winsock object.
Do you have any good suggestion ??
Thanks
April 5, 2003 at 6:42 pm
Never a bad idea to minimize connections, though sometimes that just means you're moving the work elsewhere.
Unless you have a really large number of clients or require a super low latency, I'd honestly just do polling to start with, knowing that it might not scale and you'd have to refactor. I say might not because as long as you have a decent query plan it probably wouldn't make any difference. Easy enough to test - put up a good representative sample of clients all polling at x interval, look at performance just befor the test and compare with during.
Andy
April 7, 2003 at 7:56 am
One thought I had when you mentioned that you need to limit the number of connections is to have SQL Server simply write to a file a list of whatever records you are trying to watch. In this way the db overhead is limited to one connection re-querying the db and you could then simply refresh the view of the doc from within your app whenever or however you want.
April 7, 2003 at 9:00 am
Timer querry from your application is a way to find new record right away. I have the application that does that. To minimize connection, close connection once the Timmered application read from table. Another way, you can send new record to Message Queuing which PC application is made to look at, but you have to build the mechanism when to remove the record from new record.
Hope this help.
Jie Ma
Jie Ma
April 7, 2003 at 9:36 am
Thanks for all of you
In my project, there are actually more that 30 clients will directly connect to SQL Server. Whwn me buy standard edition package which come with 5 clients. Does it means only five (my client ) on difference PC can only connect to the SQL concurrently ( or not concurrent). Almost 30 of 15 client will have their own local (MSDE) engine for data replication.
Can i just create a centre connection object and share with all of the client. But i think for replication is impossible since every MSDE must registered (as subscriber) in the server before replication can be implemented.
My final purpose is reducing the SQL client licence needed in order to implement this project.
Please help
Thank in advance
April 7, 2003 at 2:57 pm
Replication may be not the best practice here because MSDE is not support Transaction Replication.
I think that building Middle Tier(COM+) application should work for your case. All clients go through COM+ to access DB, so you can do connection pool or build DB access logic that wait and try to connect to DB if it is not available.
Jie Ma
Jie Ma
April 7, 2003 at 4:18 pm
Its been a while, but I dont think what you're doing changes the licensing requirements. It's ultimately end users that matter, not how many gates you put in between. Take a look at their internet licensing policy, you end up going per/cpu just to keep it manageable.
CAL's aren't that expensive and in my opinion (hey, I call'em like I see'em) trying to reduce them is a poor idea. Better to focus on performance, maybe save some money on hardware.
Andy
April 8, 2003 at 10:59 am
This sounds like a significant application architecture problem. First off, do your end-user clients really need immediate notification of all inserts and updates, what is the acceptable time lag, etc.
If so, then make sure you outline all key business needs. Next you need to focus on the client to middle-tier to server connection issues, to identify the best way to communicate these changes.
does the server broadcast to each client? If so how? or is each client responsible for requesting (polling) the server for the last known update time, and related information.
If you do need this type of information, then a trigger, or a middle-tier driven "trigger" is probably the way to go.
Here, with every update/insert the trigger maintains the tables that track change data.
Next, how, and where do you capture the information. Perhaps on a set of tables.
One table, with a single record, has the datetime of last known change. Your client can pole this table, and compare to see if any changes have been made. if so, next step is to request these for client update.
Or you could devise an XML/SOAP broadcast message with all the info, when changes happen.
But, you need to focus on
- business requirements
- change notification: poll or broadcast
- change data: tables to support
Good luck
What's the business problem you're trying to solve?
April 9, 2003 at 6:17 am
We have code to do that. Its pretty complicated though. In our case all the
client process when they log in hook into the
event sync of a process running on an
application server.
A COM Method was added in this process to
broadcast data changes to all the clients.
An extended stored procedure was written in
sql server to call make the com call with the
changed data. The extended stored procedure
can then be inserted into any trigger.
April 14, 2003 at 4:04 am
Thanks for all your advices
I have designed my project in this way, but don't know whether it is the right solution.
I have two type of client program
1) Buffer Client
2) Operation Client
on the(buffer Client)which need the MSDE , this is because when SQL server down, the buffer Client can store the data temporary, then upload it back when SQL server is up.
Operation Client is used to maintain database data.
Connection Issue:
All Operation Client will make one connection directly to SQL server
And the Buffer Client only make the connection base on the timer
Explain:
The buffer client will store all the data directly into local MSDE.
A component with timer object to determine the interval for synchorising the data from local to SQL server (this time only making the connection)
Synchorising data approach is applied the DTS from one table to another table (same structure table) ,so that if server on, it can get the up-to-date data base on the timer interval otherwise data remain locally
From the Operation Client this is what i mentioned a screen showing all the latesttransaction.
At this moment, Opeariotn Client still connect to SQL server to retrieve the latest
data and show on screen.
on this screen . i create a timer (interval = 7000) to keep on retrieving the latest data via stored procedure. But one problem, when retrieving large volumn data since this is a process keeping call by a time
please suggest better solution
Thank You
April 14, 2003 at 11:21 am
If this works, it could be a good approach.
With out a better understanding of the requirements, its hard to offer a better approach.
What's the business problem you're trying to solve?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply