(NOLOCK) and Linked Servers

I know that you can't have an optimizer hint when running an ad-hoc query
from another server. My question is why? What prevents it from using the
hint?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C0F66FA391C@.SQLMonster.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using the
>> hint?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
--
Andrew J. Kelly SQL MVP
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1