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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2003-02-20 : 11:23:30
|
| Hi,I'm in the process of writing an ASP.NET application which connects to a sql2k database. The database contains many tables that we will need to updated on a continuous basis (insurance application which needs updated rating data stored in the db). The new data (to be updated) will be stored in a external file (probably Access). I'm brainstorming to think of the best way to do this. Background InfoThe datafile will be located somewhere on the webserver (file will uploaded via ftp)The entire process could update around 50 - 100 tables but only 50 - 100 records per table. (so many tables, few records)DTS Solution:I know you can use DTS to load data but I'm hesitant to build a number of DTS packages on the client server. This product will be sold to many clients so I want to reduce as many parts as possible. Creating DTS packages just creates another "item" to maintain. Sproc Solution:I could write a number of Sprocs that access the Access datafile directly and load the data via a bulk insert or something. Since the datafile will be located on the webserver, I'm not sure how the database would access it. (i.e. Sproc would need to know the path TO the datafile FROM the database.)Web Solution:I could create an ASP.NET page that opens the datafile, copies the data into a dataset and then updates the sqlserver database. This problem is a mix between web and database development but I was just curious what you guys thought.ThanksNic |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-20 : 12:52:26
|
| Once the Access files are FTP'd over, you can either create linked servers to them and import that way, or use OPENROWSET to connect and query them from SQL Server. The advantage of OPENROWSET is that you don't need a linked server to the Access file. BULK INSERT would not work with a native Access file, you'd have to export from Access to flat text first. ASP.Net could work but I don't see how you could automate it, while an OPENROWSET solution could be thrown into a job and run at regular intervals. You could even include FTP tasks to upload the Access files, but it might be better to make the transfer a push rather than a pull. |
 |
|
|
|
|
|
update client database