Polling multiple databases

I am looking for ideas and the best way to get specific data from POS databases in different locations.

We currently have about 150 locations that use RMS (using msde). My goal is get information from these databases into our centralized corporate database. For now this would be a monthly process. The type of information we receive would be the same from all the locations. We are not all on the same network so each location is independent and has broadband access.

Thanks in advance for your sugesstions.

Don't ask me for details, but I'd think you could create a batch file to run an SQL query that creates a .csv file (or some other useful format). Use Task Scheduler to execute the batch file - make sure that the file is saved somewhere easy to find like C:\Monthly - then have your store managers e-mail the file to you every month. You might even be able to use task scheduler for the e-mail.

Good luck, Tom

I am looking for ideas and the best way to get specific data from POS databases in different locations.

We currently have about 150 locations that use RMS (using msde). My goal is get information from these databases into our centralized corporate database. For now this would be a monthly process. The type of information we receive would be the same from all the locations. We are not all on the same network so each location is independent and has broadband access.

Thanks in advance for your sugesstions.

You could use

1) database synchronization 2) using "osql" command line utility to execute specific query and redirect the result to a network accessible folder

In a real life scenario, we want a glance at daily transaction of all the stores so we pull the data from trasaction table using "osql" with scheduler, save the the result to a folder which is only accessible by system, then from head office, another scheduler will execute "copy /Z" command to fetch the result back to head office.

-Chris H