Design help

I wrote something like this for SIEMENS AG on SQL Server 2000 systems by using stored procedures and triggers.
The resulting method to keep everything safe was simply to use physical temp tables to dump the data in and pull it from then pumping into the SQL Servers needing to be updated, for syncing.
All automated by the SPs and triggers.


Post Edited (wisemx) : 8/30/2006 8:17:30 PM GMT

 

I posted this in the asp.net forum and got no responces. I hope the database forum can help.

Hi

I'm looking for some design help.
I have a Web Application that has data that is maintained manually. (MSSQL 2000, VB.NET, ASP.NET)
Presently I:
1) Go to 4 different Site and write down 5 pieces of information from each site (Not a lot but eventually will be 100's of sites, I will need to go to for the information)
2) Manually enter the 5 pieces of information in 4 different MSSQL 2000 tables. (Web Application User Interface)
3) Manually run 10 different Stored procedures on all 4 tables which creates 40 different tables of data from the 4 initial tables. (Web Application User Interface)

I want to automate this. Can someone give me some direction ?
The following sequence would automatically start at midnight when the discountASP would fire a web application page.

Here is what I think could be done:
1) Access Rss Feed for data. (This is already Implemented)
2) Parse Rss Feed for 5 pieces of data need. (This is already Implemented)
3) Update 4 tables in the Application (Automate, Not Implemented yet but should be able to use present code)
Here is where I need direction.
4) Run the 10 stored Procedures in the Web Application (Not Implemented yet but should be able to use present code)
OR
4) Make the 10 table dependent on the table updated in step 3 and run stored procedures in MSSQL 2000. (Not Implemented yet)
OR
4) Better suggestion.

I think this is possible but my experience is limited.
Any help with step 4 will be appreciated
Thanks for your help.
albanello

 

If you can pull all the data you need into a SQL table you can pump it back out in any flavor needed via triggers and stored procedures.


Triggers work great for this, when data is updated for example the trigger can perform an action for you.


Once completed a SP can shuffle the data any way you want, from there you can distribute it.


To keep it safe always use a physical temp table where you make these changes, which is much safer than running those actions against the actual data tables.


At the end of each pump you can trash the temp tables, with a variety of methods, this way you won't even need to worry about trapping duplicates.
btw, are you by any chance Brasilian? (My wife is and that name looks, um, legal.)

 

wisemx


Thanks again for your reply.


I think I am going to go with a trigger and let MSSql do the work of updating the 10 tables. Now all I did to do is dig out my MSSql books and implement the trigger and stored procedures.


albanello is italian


Thanks again


albanello

 

wisemx


Thank you for your reply.


So you are saying:


1) Fire a Web Application Page that does a Rss Feed read and updates the Base Table.


2) Place triggers on the 10 table to fire the stored procedure automatically when the base table changes.


Thanks again


albanello