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 |
|
moleme
Starting Member
1 Post |
 Posted - 2010-03-25 : 11:11:03
|
Hello, I was looking for suggestions on the best possible solution for the following problem.Currently we have a ASP.NET application (the "ASP app") that is being hosted on a external server (MS-SQL Server 2000 SP4); through the front-end of the application a user is allowed to "submit" a set of data to be reviewed by another user of the application. This data can either be accepted, deleted or sent back to the intial user for update. Now the user who's job is to review the submission uses a separate application (the "processing app") as there is a process surrounding the approval of the data. This application is hosted on a physically separate server and runs MS_SQL Server 2005 SP2. Whenever a submission is processed by the ASP app I need to create a new row in the DB for the processing app. Now there are a number of ways to accomplish this and I have briefly explored the following: - Update the ASP app so that it inserts rows into both databases - Possibly the best solution but not available to us (too much money)
- Use a trigger on the table that is updated upon submission through the ASP app. This trigger would run a SSIS(if possible on SQL 2000) using the sp_start_job stored procedure
- Schedule a job to look for updates on the ASP app DB every 15 minutes, this then connects to the processing app DB and updates the corresponding table
- Scheduling a SSIS package on the processing app DB to check for changes on the ASP app DB every 15 minutes
- Using Service Broker or Notification Services - I do no think this is possible with SQL Server 2000?
Those are some of the options I have brainstormed; I was wondering if anyone has had a similar problem and if they addressed it using 1 of the scenarios listed above or another way.Any help is greatly appreciated.Andrew |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-25 : 13:35:51
|
| Not sure I have enough details on what you're doing, but sounds like a job for Transactional Replication to me |
 |
|
|
|
|
|
SSIS/DTS from SQL 2000 to SQL 2005