I've just been given a Lulu of a project at work involving a Paradox DB not housed locally, an Access housed locally, and SQL Server 2005 DB housed locally, and I have no idea really how to proceed. I'm hoping you guys can help.
I am migrating a classic ASP application that gets a ton of traffic (its a plants database for the state of Wisconsin that gets a lot of traffic from the federal gov't and other universities). Currently what happens is once a week we get a download from a Paradox DB that then gets saved as an Access DB that contains changes that need to be made to the Access DB which is what is running the site now. Then, in the middle of the night we copy the new Access DB to the server under a new name, test it, then move the old DB to an archive location and rename the new Access DB to what the odl one was named so as to not interrupt site function.
I have set up a SQL Server 2005 DB to take the place of the Access DB so no issue there. The SQL DB has all the tables it would need to replace the Access DB once I get the ASP.NET version of the website built. I am however lost in that I need to find a way to automate (if possible) the inserting, updating, and deleting of records in various tables in the SQL DB based on what I receive each week from the Paradox DB download. Some of these tabels are huge and have over 10,000 records in them so efficiency is important.
Is there a way to automate this? If there is no way to automate this, is there an easy way to do this if the updates, deletes etc are XML Files in stead of an Access database? Any advice you have would be more than welcome...
First off, MS Access is not a good idea in my opinion since MS SQL will do the trick. Nice to hear you want to phase it out.
Is there an option for ParadoxDB to provide a view ? Or is there an option to have a view on MS SQL to look at the fields on the ParadoxDB? From here, all you have to do is create a GUI.
OR
How about having ParadoxDB dump a file (flat, excel, xml) directly on the MS SQL Server at 1:00am? Then automate the MS SQL Server to import the file via aDatabase Maintenance Plan at 1:30am. This way will refresh the data on the MS SQL with the most up-to-date data. Then build your GUI.
Had another thought...
Can you have the Paradox dump Only Changes? That way the number of records will not be as large, hence it will take less time.
In the MS SQL Database Maintenance Plan, you can set it to overwrite or update changes.
Then create another Maintenance Plan to backup your data at 2:00 am.
Wow. Thanks for getting back to me so fast.
I'm not sure what "view" is or how to create one, much less how to create one that is not on a server that I have direct access to or use it in an ASP.NET website. I should have said earlier, I am fairly new to databases, and usingthem to create robust dynamic sites, but I am willing (and eager) to learn...
I do have access to the SQL server, just not the server with the Paradox DB, so I could create a view for the various tables on the SQL, but then I'll need to know where to go to learn how to use them in ASP.NET to do what I need to do.
I would presume I could arrange for the Paradox to dump an XML file. Currently it dumps a Paradox table that gets exported as an Access db. When it does this, it only has the changes to the tables.
Got some links on where I can learn to make / use one of these maintenance plans? Will I need Admin rights on the SQL Server DB to do this? Maintenance plans or views? Links to some tutorials might be good as well.
0 comments:
Post a Comment