I somewhat have it working by creating a SP on destination server to insert into local table the top 500 records from remote source table and running it as an sql agent job. However, now I’m finding out that I need to actually insert the new records to another database on a remote server, on another subnet which is firewalled. There is only a one way communication between the 2 subnets. The new remote server destination is running sql 2005 standard. When I publish replication from 2005 sql and tried to subscribe from 2008 sql (different subnet), SQL complained that subscriber version can’t be newer than publisher. I can’t switch publisher and subscriber between servers because of the one way communication.
This is getting more and more complex for my SQL experience (very limited), 3 sql servers running 3 different versions (Express, 2008 standard and 2005 standard), 2 subnets with one way communition between the 2. I need to put this on hold for now, I have another project that needs to get done soon.
Thank you for your help.