forcing remote evaluation of like filters with linked servers

i have server1
server 2 is linked to server1
server 3 is linked to server1
all sql2005
on server1 i go
select foo from [server2].[db].[dbo].table where bar like '%ff%'
union all
select foo from [server3].[db].[dbo].table where bar like '%ff%'
by looking at the execution plan I can see that the remote selects have no
filter on them and that the filtering is applied locally by server1. this is
of course extremely inefficient.
The docs say that if I say thet the provider has sql like capability then
the query planner 'might' consider asking the remote server to do the like
processing.
I did that (even though it seems redundant since server1 knows that this is
another sql sevrer database).
What other magic buttons do I have to pressHi Paul
Using OPENQUERY would allow you to filter remotely.
John
"paul moore" wrote:
> i have server1
> server 2 is linked to server1
> server 3 is linked to server1
> all sql2005
> on server1 i go
> select foo from [server2].[db].[dbo].table where bar like '%ff%'
> union all
> select foo from [server3].[db].[dbo].table where bar like '%ff%'
> by looking at the execution plan I can see that the remote selects have no
> filter on them and that the filtering is applied locally by server1. this is
> of course extremely inefficient.
> The docs say that if I say thet the provider has sql like capability then
> the query planner 'might' consider asking the remote server to do the like
> processing.
> I did that (even though it seems redundant since server1 knows that this is
> another sql sevrer database).
> What other magic buttons do I have to press
>
>|||thanks - works great
seems like the 'normal' query processor should do it automatically though -
defaulting to the most inefficient way is surprising
"John Bell" wrote:
> Hi Paul
> Using OPENQUERY would allow you to filter remotely.
> John
> "paul moore" wrote:
> > i have server1
> > server 2 is linked to server1
> > server 3 is linked to server1
> > all sql2005
> >
> > on server1 i go
> > select foo from [server2].[db].[dbo].table where bar like '%ff%'
> > union all
> > select foo from [server3].[db].[dbo].table where bar like '%ff%'
> >
> > by looking at the execution plan I can see that the remote selects have no
> > filter on them and that the filtering is applied locally by server1. this is
> > of course extremely inefficient.
> >
> > The docs say that if I say thet the provider has sql like capability then
> > the query planner 'might' consider asking the remote server to do the like
> > processing.
> > I did that (even though it seems redundant since server1 knows that this is
> > another sql sevrer database).
> >
> > What other magic buttons do I have to press
> >
> >
> >|||Hi
I think if you were not using like you would be ok, but I haven't tested it!
John
"paul moore" wrote:
> thanks - works great
> seems like the 'normal' query processor should do it automatically though -
> defaulting to the most inefficient way is surprising
> "John Bell" wrote:
> > Hi Paul
> >
> > Using OPENQUERY would allow you to filter remotely.
> >
> > John
> >
> > "paul moore" wrote:
> >
> > > i have server1
> > > server 2 is linked to server1
> > > server 3 is linked to server1
> > > all sql2005
> > >
> > > on server1 i go
> > > select foo from [server2].[db].[dbo].table where bar like '%ff%'
> > > union all
> > > select foo from [server3].[db].[dbo].table where bar like '%ff%'
> > >
> > > by looking at the execution plan I can see that the remote selects have no
> > > filter on them and that the filtering is applied locally by server1. this is
> > > of course extremely inefficient.
> > >
> > > The docs say that if I say thet the provider has sql like capability then
> > > the query planner 'might' consider asking the remote server to do the like
> > > processing.
> > > I did that (even though it seems redundant since server1 knows that this is
> > > another sql sevrer database).
> > >
> > > What other magic buttons do I have to press
> > >
> > >
> > >

For Loop help

Dear Friends,
I am getting problem in the following statement.
Please help.
ALTER PROCEDURE dbo.Defaulters
(@.Stdt smalldatetime)
AS
declare @.CNT int
select @.cnt = 1
FOR @.cnt <= 7
Select dbo.Employee_Master.Employee_Name
@.stdt =@.stdt +1
@.cnt = @.cnt + 1
Next
Best regards
SharadIs this a CLR stored procedure written in VB.NET?
"Shailesh" wrote:

> Dear Friends,
> I am getting problem in the following statement.
> Please help.
> ALTER PROCEDURE dbo.Defaulters
> (@.Stdt smalldatetime)
> AS
> declare @.CNT int
> select @.cnt = 1
> FOR @.cnt <= 7
>
> Select dbo.Employee_Master.Employee_Name
> @.stdt =@.stdt +1
> @.cnt = @.cnt + 1
> Next
>
> Best regards
> Sharad
>
>|||Shailesh (shailesh_gothal@.hotmail.com) writes:
> I am getting problem in the following statement.
> Please help.
> ALTER PROCEDURE dbo.Defaulters
> (@.Stdt smalldatetime) AS
> declare @.CNT int
> select @.cnt = 1
> FOR @.cnt <= 7
>
> Select dbo.Employee_Master.Employee_Name
> @.stdt =@.stdt +1
> @.cnt = @.cnt + 1
> Next
I'm sorry, but I'm afraid that a newsgroup is not the right venue for you
to help. A newsgroup is good when you have a specific question. But it's
not a very efficient place to learn the subject from the bottom.
If all you want help with is to write a control loop, I refer you to
the topic "Control-of-Flow Language" in the Transact-SQL Reference in
Books Online. The reason I give you "read-the-manual" answer, is that I
think that are better served by learing to use the manual. While it may
be with some resistence in the beginning, it pays off in the long run.
Now, even with the proper syntax the procedure would not make much sense.
You would select all rows in the table Employee_Name seven times. (Provided
that you have a database called dbo, that is!) This does not look like a
useful operation.
I would suggest that you should find some SQL Server training locally, our
get a book like Richard Waymires "SQL Server 2000 in 21 days". Playing
around and inventing your own syntax will only be frustrating and
ineffecient.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx