we are trying to execute a remote sql query from one sql server against another. Both servers are in the same domain. The query is a simple select * from tablename where surname = whatever. We precede this with an 'opendatasource' statement. The query returns only a few rows. When we run the query between two sql servers on the same lan segment the query runs in a second or two. When we run the query between servers on two different network segments (seperated by a firewall) but still both in the same domain the same query takes 50+ seconds to run. Looking further, it seems that the query is actually being executed on the local server so the entire table (over 1 million rows) is copied locally before running the query. How can I run the query as a remote query so that the entire query is run on the remote sql server and only the query results are passed accross the network to the local server? I have also tried creating a linked server and using OPENQUERY but still takes ages to run.
02-07-2006, 08:34 AM
The below link contains some tips, although you may have already attempted a couple of them: