hey all, just signed up because i have a problem i just cannot figure out. i may cross-post
as i think this is more of an sql asp problem than sql server...
ok first what i'm trying to do - i have one server with records that contain row addresses. let's say it's a customer and row address 1234. i also have another db server that contains the application and the actual row addresses. here that address for that customer should be 1234 - sometime it's not, and the original server is pointing to the wrong one. i want to identify these records.
the page needs to do this -
get records from server1
get records from server2
compare records and display results.
i have tried this varying ways which resulted in different speeds. it's working pretty good now :D
but only when the webserver running this is in the same building. unfortunately my other 2 servers are often located elsewhere, elsewhere being in europe and australia, while the webserver is in illinois.
i figure probably the quickest way to do the compare now is with sql to 2 text file result sets from the servers, (which i haven't mastered yet either) - but getting those resultsets is where my problem lies. i tried pulling down the resultset and writing it to text, iterating with movenext through my rs. it was taking forever, so it occured to me that i'm not pulling the whole rs to memory on my webserver, just one record at a time.
i spoke to someone that recommend i try getting everything all at once as XML, and had high hopes :D unfortunately i have some of those running in the background now, and writing that xml file seems to be taking just as long. i opened my recordset and just did a .save after that, which seems like it may be just slowing streaming that line by line across the world as well.
i am hoping this program to handle up to 50k lines from each source. each line is only 2 or 3 short fields. i need to get this resultset into memory or a local file on my webserver to do the compare, i think best would be an sql connection and join onto 2 text files, but building them in a timely manner is really presenting quite a challenge to me.
i do not want to add/alter anything on the source databases, and i have high doubts that i am allowed to at all. any ideas
http://www.developer10.com
Interesting Challenge... need remote RS quickly
