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

I have a similar situation at my office although the servers aren't so far apart :), one is in Bristol, the other Birmingham.
I run a distributed query using OPENROWSET to create two virtual tables and join them using a LEFT OUTER JOIN, I then filter for all the rows where the key field is NULL in the right hand table.
Definitely works and SQL seems to optimise it well, it doesn't bring back all rows and then filter for example. No idea how this would perform in your scenario though.

--

Joe

thanks joe! that is working great - both target servers are in the same solution center so they are fine connecting to eachother, and i just pull the resultset now. works great!


http://www.developer10.com