linked view in access don't use index

Hello,
I have a table with two field (name of the table : table_with_two_field)
field1, field2
when i create a simple view with no clause where:
create view dbo.view_on_field WITH VIEW_METADATA
as
select field1 from dbo.table_with_two_field
and i linked this wiew on access 2000 and i join with a local table on
field1 i have not response (field1 is a primary key on local and field1
have an index on the sql server table table_with_two_field)
in the trace i can see sql server send all the data : "select field1 from
dbo.view_one_field"
but when i do the same with local table and attached table i have a reponse
directly
can you help me
I'm not sure what precisely you are trying to do, but any time you
fetch data from the server and perform a join to a Access/Jet table,
ALL of the data is fetched from the server, and the join performed
locally by Jet. I am assuming that since you are performing a
heterogeneous join that you are not intending to update the data. What
I would suggest is to select from the SQLS table or view using a WHERE
clause to limit the result set, and dump it into a local Access/Jet
table, which should be very fast since you have fetched the data
locally and eliminated the heterogeneous join. The same local table
can be reused multiple times by deleting all of the rows before
fetching new ones.
--Mary
On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:

> Hello,
> I have a table with two field (name of the table : table_with_two_field)
> field1, field2
> when i create a simple view with no clause where:
>create view dbo.view_on_field WITH VIEW_METADATA
>as
> select field1 from dbo.table_with_two_field
>and i linked this wiew on access 2000 and i join with a local table on
>field1 i have not response (field1 is a primary key on local and field1
>have an index on the sql server table table_with_two_field)
>in the trace i can see sql server send all the data : "select field1 from
>dbo.view_one_field"
>but when i do the same with local table and attached table i have a reponse
>directly
>can you help me
|||Hello Mary,
I try to explain my problem, sorry for my english because i'm spain
when i use directly a linked table with a local table, i have data
immediatly, when I
see the trace, Access send every value field (use in join) from data from
the local table to the sqlserver.
The profiler trace send :
declare @.P1 int
set @.P1=2
exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
select @.P1
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
exec sp_execute 2, N'925000029010202146'
Local table have 3 records
when i do a simple view like this
create view dbo.view as select field1 from dbo.table1
when i use directly this linked view with a local table,
the trace send :
SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft? Access etc..
and slqserver send millions records to access ...
Why the linked view and the linked table not have the same reaction ?
I can't do a view with more filter because every user have his owner local
table.
Thank you very much for your help.
I can
Rega1
"Mary Chipman [MSFT]" wrote:

> I'm not sure what precisely you are trying to do, but any time you
> fetch data from the server and perform a join to a Access/Jet table,
> ALL of the data is fetched from the server, and the join performed
> locally by Jet. I am assuming that since you are performing a
> heterogeneous join that you are not intending to update the data. What
> I would suggest is to select from the SQLS table or view using a WHERE
> clause to limit the result set, and dump it into a local Access/Jet
> table, which should be very fast since you have fetched the data
> locally and eliminated the heterogeneous join. The same local table
> can be reused multiple times by deleting all of the rows before
> fetching new ones.
> --Mary
> On Mon, 3 Oct 2005 01:17:01 -0700, "toni"
> <toni@.discussions.microsoft.com> wrote:
>
|||I'm hazarding a guess here, but I suspect the reason why is because
Access cannot discover the schema for the view. Access caches schema
information for linked tables locally, so it can create the
parameterized prepared statement you are seeing in the first query.
Based on the Profiler output you posted, it *may* not do that for
views, so Access/Jet possibly has no idea of what the schema for the
view's underlying table is. Therefore it fetchs all of the data. You
might want to post in one of the msaccess forums in case the experts
there have a more definitive response.
--Mary
On Tue, 4 Oct 2005 05:35:02 -0700, "toni"
<toni@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Hello Mary,
> I try to explain my problem, sorry for my english because i'm spain
> when i use directly a linked table with a local table, i have data
>immediatly, when I
> see the trace, Access send every value field (use in join) from data from
>the local table to the sqlserver.
>The profiler trace send :
>declare @.P1 int
>set @.P1=2
>exec sp_prepexec @.P1 output, N'@.P1 nvarchar(20)', N'SELECT "field1" FROM
>"dbo"."table1" WHERE ("field1" = @.P1)', N'925000029010202145'
>select @.P1
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>exec sp_execute 2, N'925000029010202146'
>Local table have 3 records
>when i do a simple view like this
>create view dbo.view as select field1 from dbo.table1
> when i use directly this linked view with a local table,
>the trace send :
>SQL:BatchCompletedSELECT field1 FROM "dbo"."view " Microsoft Access etc..
>and slqserver send millions records to access ...
>Why the linked view and the linked table not have the same reaction ?
>I can't do a view with more filter because every user have his owner local
>table.
>Thank you very much for your help.
>I can
>Rega1
>
>
>
>"Mary Chipman [MSFT]" wrote:
sql

server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoB
Hi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:

> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/default...b;en-us;276225
http://groups.google.ch/groups?q=sql...al&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=en...GP09.p hx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon
|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...[vbcol=seagreen]
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
a[vbcol=seagreen]
where[vbcol=seagreen]
The[vbcol=seagreen]
contains[vbcol=seagreen]
It[vbcol=seagreen]
it
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/default...b;en-us;276225
>
http://groups.google.ch/groups?q=sql...collation& hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=en...org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.p hx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>

server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoBHi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:

> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's a
sk
> me why)
> So it looks like if there is a selection on a text field (varchar) the whe
re
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, cou
ld
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/defaul...kb;en-us;276225
http://groups.google.ch/groups? q=s...ial&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=e...FTNGP09.phx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
a[vbcol=seagreen]
where[vbcol=seagreen]
The[vbcol=seagreen]
contains[vbcol=seagreen]
It[vbcol=seagreen]
it[vbcol=seagreen]
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/defaul...kb;en-us;276225
>
http://groups.google.ch/groups? q=s...ati
on&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=e...=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>

server: WHERE clause not being executed

Hi all,

One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.

Query: select * from oas_company where code = 'TEST'

If I look on serverB via Profiler the query is executed without the where
statement.

Query: select * from oas_company where tstamp = 0

Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)

So it looks like if there is a selection on a text field (varchar) the where
statement is not included.

For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.

Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?

Any suggestions would be greatly appreciated.

BoB"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB

Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?

select * from serverB.MyDB.dbo.oas_company where code = 'TEST'

You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:

http://support.microsoft.com/defaul...kb;en-us;276225
http://groups.google.ch/groups?q=sq...ial&sa=N&tab=wg

The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:

http://groups.google.ch/groups?hl=e...FTNGP09.phx.gbl

If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.

Simon|||Thanks for your reply. We will investigate your solution asap.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
> > Hi all,
> > One of our companies is having problems with a query on a linked server.
> > They have two servers, serverA and serverB. On serverA they have set up
a
> > linked server to serverB.
> > Query: select * from oas_company where code = 'TEST'
> > If I look on serverB via Profiler the query is executed without the
where
> > statement.
> > Query: select * from oas_company where tstamp = 0
> > Again via profiler I can see that the where statement is now included.
The
> > tstamp field is a numerical field, the code field is a text field (don's
> > ask
> > me why)
> > So it looks like if there is a selection on a text field (varchar) the
> > where
> > statement is not included.
> > For the above selection this is not a problem as this table only
contains
> > 10 - 20 records. However the real problem is on the oas_balance table.
It
> > should only return 3 records, but because it skips the where statement,
it
> > returns 10.000.000 records.
> > Any idea? I know the default collations are different on both servers,
> > could
> > that be the cause of the problem? And if so, what could I do to overcome
> > this problem?
> > Any suggestions would be greatly appreciated.
> > BoB
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/defaul...kb;en-us;276225
http://groups.google.ch/groups?q=sq...%20collation&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=e...=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon

server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoBHi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/default.aspx?scid=kb;en-us;276225
http://groups.google.ch/groups?q=sql%202000%20linked%20server%20collation&hl=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
> > Hi all,
> >
> >
> >
> > One of our companies is having problems with a query on a linked server.
> > They have two servers, serverA and serverB. On serverA they have set up
a
> > linked server to serverB.
> >
> > Query: select * from oas_company where code = 'TEST'
> >
> > If I look on serverB via Profiler the query is executed without the
where
> > statement.
> >
> > Query: select * from oas_company where tstamp = 0
> >
> > Again via profiler I can see that the where statement is now included.
The
> > tstamp field is a numerical field, the code field is a text field (don's
> > ask
> > me why)
> >
> > So it looks like if there is a selection on a text field (varchar) the
> > where
> > statement is not included.
> >
> > For the above selection this is not a problem as this table only
contains
> > 10 - 20 records. However the real problem is on the oas_balance table.
It
> > should only return 3 records, but because it skips the where statement,
it
> > returns 10.000.000 records.
> >
> > Any idea? I know the default collations are different on both servers,
> > could
> > that be the cause of the problem? And if so, what could I do to overcome
> > this problem?
> >
> >
> >
> > Any suggestions would be greatly appreciated.
> >
> >
> >
> > BoB
> >
> >
> >
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;276225
>
http://groups.google.ch/groups?q=sql%202000%20linked%20server%20collation&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=en&lr=&client=firefox-a&rls=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>