Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
khateeb82
Starting Member
4 Posts |
 Posted - 2008-02-01 : 17:57:50
|
| hi allim really have a problem in my project.i have server and client side each side contain SQL Server DB. and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed. now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).my problem :u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).so i do this to know changed cell range:when run the program saved all excel tabel into SQL server table.and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.and rise event to send this row to client by socket over internet to update the row in client side too.but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)thank u.AL-Khateeb |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-01 : 23:41:21
|
| I think that you might think of using DTS here as its the first thing that comes to my mind. There might be some other efficient methods. What i would have done is to have a DTS package which dumps the data onto a staging table as it is. the table will have an additional audit column datemodified which will default to GETDATE(). Each DTS run will look for those records in staging table with datemodified > lastdtsrundate (which you need to store in a dts log table). Thus each time it will pick up the delta changes from staging table which can be then compared with server/client side db to perform new inserts/updates/deletes ( i hope all these tables have datemodified audit columns) and each dts run will put the current dts run date in log table which will be used as reference by subsequent runs. You may schedule a job for dts running with a suitable frequency so that job will run it in prescribed frequency. Hope this is what you are looking at. |
 |
|
|
khateeb82
Starting Member
4 Posts |
Posted - 2008-02-02 : 11:54:48
|
| thank u so much visakh for ur replay.but this is a complex way.i want to simplest way.but really thank u visakh. DTS need to close excel sheet when i export the data.and i cant close the sheet coz excel sheet always updated from other server. ???what i do ? |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-06 : 14:36:32
|
| Ok, let me see if I understand. You have an Excel file that is located on a server and there is a process that updates the file all the time. Most of the updates are less than a second apart. You need to update tables in both a server and a client with EVERY change that happens to the Excel file. Are you writing a log of changes so that every change can be seen (Example) or are you just making the current data in the Excel file available in the databases? If you are just making the current data available, then you don't necessarily need to trap every event (depending on your Real Time needs) and can just truncate and reload the table every X amount of time. Otherwise, I don't think that SSIS is an appropriate tool. Let me know and I'll see if I can help.Example:2/6/2008 12:30:00:100, Val1 changed to 52/6/2008 12:30:00:200, Val1 changed to 152/6/2008 12:30:00:300, Val1 changed to 92/6/2008 12:30:00:400, Val1 changed to 12 |
|
|
|
|
|
|
|
|
Cell Changed Each less than 1 sec