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

SD9999 gravatar image

SD9999
0 1 1 1

(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

SD9999 gravatar image

SD9999
0 1 1 1

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x106

asked: May 29, 2012 at 03:17 PM

Seen: 710 times

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