matthewshar
09-15-2008, 09:15 AM
From what I've read, DiscountASP does not allow the use of xp_cmdshell or DTS / BCP, etc. I also understand that instead of creating SQL Server jobs, you are supposed to use the task scheduler control panel to initiate a call to a web page or web service that will complete the task of a job.
Given that, is there any possible way to do the following with shared hosting in a fully automated fashion on a daily basis:
1. Download database updates in flat files that are on an FTP server.
2. Add the data in the text file to the database.
The only way I could think of is to have a process that runs on my local home desktop PC that pulls the file to my local C: drive and then calls a web service on localhost which connects to my discountasp database and literally iterates through the records one at a time and does the appropriate update and/or insert for each record in asp.net server side code. This would be extremely slow and put a tremendous unnecessary load on the database server; plus the process would rely on my home machine being on and connected to the Internet which is a real hack.
Ideally, I'd like to have a sql job that uses xp_cmdshell to ftp a file to the discountasp server and use a dts package to import the raw data into a staging table and then perform the neceessary sql commands between the staging table and the other tables in my database; but it looks like that is prohibited for obvious security reasons. Is there a creative work-around that anyone can offer up that would be more efficient and reliable than my home PC one record at a time hack?
Given that, is there any possible way to do the following with shared hosting in a fully automated fashion on a daily basis:
1. Download database updates in flat files that are on an FTP server.
2. Add the data in the text file to the database.
The only way I could think of is to have a process that runs on my local home desktop PC that pulls the file to my local C: drive and then calls a web service on localhost which connects to my discountasp database and literally iterates through the records one at a time and does the appropriate update and/or insert for each record in asp.net server side code. This would be extremely slow and put a tremendous unnecessary load on the database server; plus the process would rely on my home machine being on and connected to the Internet which is a real hack.
Ideally, I'd like to have a sql job that uses xp_cmdshell to ftp a file to the discountasp server and use a dts package to import the raw data into a staging table and then perform the neceessary sql commands between the staging table and the other tables in my database; but it looks like that is prohibited for obvious security reasons. Is there a creative work-around that anyone can offer up that would be more efficient and reliable than my home PC one record at a time hack?