October 30, 2006 at 11:33 am
I am creating a query to hit a sql server database backend to a 3rd party application. The goal is to pull active projects on a nightly basis into a warehouse combining data from different applications. The linking server is sqlserver 2005 the linked server is sqlserver 2000.
SQL Example:
INSERT INTO mytable (yada1, yada2)
SELECT yada1, yada2
FROM mylinkedServer.thirdPartyAppDB.dbo.targetTable
WHERE projectid in (SELECT projectid from myprojects where active=1)
I have examined the execution plan and made some test queries. The performance indicates the entire table is not being pulled to my linking server. Basically I am trying to confirm that even though this is a linked server that the query is executing on the remote server.
all thoughts appreciated
thanks
October 30, 2006 at 11:51 am
It's quite possible and actually the best possible plan you can have. So unless you re getting unexpected results, you are golden to move forward with it.
October 31, 2006 at 1:51 pm
thanks, guess I was just confirming that my eyes were focused...
October 31, 2006 at 3:51 pm
if you are unsure about pulling the whole table over, you can always do this: INSERT INTO ... EXEC linked.db.dbo.sproc - then you can always be absolutely certain that the filtering is happening remotely.
also you are insulating yourself against changes on the remote if you use a sproc.
Note that this isn't supported if you do a loopback linked server though, in fact I know it won't work (I tried it once!). Actually loopback scenarios with linked servers aren't supported at all by ms.
---------------------------------------
elsasoft.org
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply