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 XML on Server 7

One of my remote servers is still SQL Server 7.
I was trying FOR XML AUTO query on it,
and it giving me Incorrect syntax near 'XML'.
The question is if XML queries can't be done on Server 7 at all,
or there is a way to make it working?
Thanks,
Michael
FOR XML does not work on SQL Server 7 or older.
But if your local server is 2000 or newer, you could use the FOR XML on the
local instance on the result of the remote query.
HTH
Michael
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
> One of my remote servers is still SQL Server 7.
> I was trying FOR XML AUTO query on it,
> and it giving me Incorrect syntax near 'XML'.
> The question is if XML queries can't be done on Server 7 at all,
> or there is a way to make it working?
> Thanks,
> Michael
>
|||I remember MS did offer a beta when 2000 was not out yet,
it was an ISAPI .dll that offered the same functionality
as in 2000 but for 7. But that was late '99. You'd have a
rough time tracking it down.
>--Original Message--
>FOR XML does not work on SQL Server 7 or older.
>But if your local server is 2000 or newer, you could use
the FOR XML on the[vbcol=seagreen]
>local instance on the result of the remote query.
>HTH
>Michael
>"MichaelK" <michaelk@.gomobile.com> wrote in message
>news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
7 at all,
>
>.
>
|||That ISAPI is not supported and cannot be used through linked servers...
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0f8801c4f1e0$9a9daed0$a301280a@.phx.gbl...[vbcol=seagreen]
>I remember MS did offer a beta when 2000 was not out yet,
> it was an ISAPI .dll that offered the same functionality
> as in 2000 but for 7. But that was late '99. You'd have a
> rough time tracking it down.
> the FOR XML on the
> 7 at all,

FOR XML on Server 7

One of my remote servers is still SQL Server 7.
I was trying FOR XML AUTO query on it,
and it giving me Incorrect syntax near 'XML'.
The question is if XML queries can't be done on Server 7 at all,
or there is a way to make it working?
Thanks,
MichaelFOR XML does not work on SQL Server 7 or older.
But if your local server is 2000 or newer, you could use the FOR XML on the
local instance on the result of the remote query.
HTH
Michael
"MichaelK" <michaelk@.gomobile.com> wrote in message
news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
> One of my remote servers is still SQL Server 7.
> I was trying FOR XML AUTO query on it,
> and it giving me Incorrect syntax near 'XML'.
> The question is if XML queries can't be done on Server 7 at all,
> or there is a way to make it working?
> Thanks,
> Michael
>|||I remember MS did offer a beta when 2000 was not out yet,
it was an ISAPI .dll that offered the same functionality
as in 2000 but for 7. But that was late '99. You'd have a
rough time tracking it down.
>--Original Message--
>FOR XML does not work on SQL Server 7 or older.
>But if your local server is 2000 or newer, you could use
the FOR XML on the
>local instance on the result of the remote query.
>HTH
>Michael
>"MichaelK" <michaelk@.gomobile.com> wrote in message
>news:%23TS1r$J8EHA.2552@.TK2MSFTNGP09.phx.gbl...
7 at all,
>
>.
>|||That ISAPI is not supported and cannot be used through linked servers...
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0f8801c4f1e0$9a9daed0$a301280a@.phx.gbl...
>I remember MS did offer a beta when 2000 was not out yet,
> it was an ISAPI .dll that offered the same functionality
> as in 2000 but for 7. But that was late '99. You'd have a
> rough time tracking it down.
> the FOR XML on the
> 7 at all,

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
NewishHi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:
> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
Newish
Hi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
NewishHi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and yo
u
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly droppe
d
> when there are large waves that obscure the transmitter/receiver, and ther
e
> can't be too many more demanding replication setups than this, yet the mer
ge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .