x

Linked servers and sp_execute with FOR BROWSE

Hi, I've a query like so

     SELECT X
     FROM LocalTable WHERE somefield = 1
     EXCEPT
     SELECT X
     FROM LINKEDSERVER.DB.DBO.LinkedServerTable

If I run this, I get multiple calls to

exec sp_execute 100618, XXX

If I change the query to

     SELECT X
     FROM LocalTable WHERE somefield = 1
     EXCEPT
     SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT X
     FROM DB.DBO.LinkedServerTable FOR BROWSE')

The multiple calls are removed and replaced with a single, simple

SELECT X FROM DB.DBO.LinkedServerTable FOR BROWSE

I would like to know if this is a 'good' way to avoid the linked server sp__execute issue as this table may have millions of records in it soon. the local table also has millions. If the answer is to copy the remote table across I'm happy with that. Still would like an explanation of why FOR BROWSE helps though. I have tried it without the FOR BROWSE and it still executes one sp_execute per remote row

help appreciated.

more ▼

asked May 29, 2012 at 03:17 PM in Default

avatar image

SD9999
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

scrub that. The OPENQUERY was all that is required. The profile I was looking at was picking up the sp_execute from the very job I wanted to fix. FOR BROWSE doesn't matter.,

more ▼

answered May 29, 2012 at 04:07 PM

avatar image

SD9999
0 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x135

asked: May 29, 2012 at 03:17 PM

Seen: 952 times

Last Updated: May 29, 2012 at 04:07 PM

Copyright 2016 Redgate Software. Privacy Policy